• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 648
  • Last Modified:

Convert Specific columns to specific nodes (XLS to XML)

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!
Antonio King
Antonio King
2 Solutions
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 http://www.rentacoder.com/RentACoder/default.asp

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
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?


Forced accept.

EE Admin

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now