Convert Specific columns to specific nodes (XLS to XML)

Posted on 2007-10-04
Last Modified: 2012-05-05
I would like to generate an xml file from a Microsoft Excel Spreadsheet...
The XML file should look like so...

<?xml version="1.0" encoding="utf-8"?>
  <company currentbalance="" creditlimit="" availablebalance="" creditdecision=""></company>

currentbalance, creditlimit, availablebalance, creditdecision and company will all be pulled from an Excel spreadsheet.

The company names need to be pulled from column C starting from Row 9 until there is no content.
The currentbalance from coloumn D down to where coloumn C stops.
The creditlimit comes from coloumn E down to where coloumn C stops.
The availablebalance comes from coloumn F down to where coloumn C stops.
The creditdecision comes from coloumn K down to where coloumn C stops.

I dont mind how this is done. C# would be preferred but vb, vbs or batch scripting are fine too.

Thanks in advance!
Question by:Alan-Yeo
    LVL 5

    Accepted Solution

    You have several options here.  How ever it looks like you are asking for an entire solution.  Its too much work for someone to write it for you.  I would suggest one of the pay for coding sites. like

    If you are just looking for some help you can.

    1. Create a macro to create a xmldocument and populate it from the cells.
    Much more difficult
    2. write a program to read the excel file and create a xmldocument and populate it from the cells.
    a. use ado to read the excel file : easier
    b. use third party component to read the excel file. Like syncfusion : harder
    c. use PIAs for office and read to read the excel file. : hardest
    LVL 65

    Assisted Solution

    Hi, if you just manually convert that XLS to an XML file, by using this code in a macro:
        ActiveWorkbook.SaveAs Filename:="C:\temp\Scripts\xls_to_xml.xml", _
            FileFormat:=xlXMLSpreadsheet, ReadOnlyRecommended:=False, CreateBackup:= _

    Does that give you an XML file close to what you need?


    LVL 1

    Expert Comment

    Forced accept.

    EE Admin

    Featured Post

    Gigs: Get Your Project Delivered by an Expert

    Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

    Join & Write a Comment

    How to remove superseded packages in windows w60 or w61 installation media (.wim) or online system to prevent unnecessary space. w60 means Windows Vista or Windows Server 2008. w61 means Windows 7 or Windows Server 2008 R2. There are various …
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now