Wednesday, 14 January 2015

Export MS Excel named Ranges to XML

A simple MS Excel application which allows opening another MS Excel workbook and then saves all the named ranges to an XML file.  Whilst Excel permits saving workbook as an XML file, or exporting data to an XML file, such is of little use for data randomly scattered throughout the workbook.

The application exports the data tagged as parameters, variables, lists and tables. If a named range is a single cell and unprotected then it tags it as a parameter. If the single cell is not unprotected then it is tagged as a variable. A named range which consists of a single row or column of data is tagged a list, whilst a named range comprising of rows and columns is tagged a table.

It doesn't use any XML library, it simply writes the data to a simple text file with appropriate tags. Also it uses FileSystem objects to write to the file, as the standard vba print statements do not recognise unicode characters, where as Excel cells can contain such.

Spreadsheet can be down loaded: exportNamedToXML.xls

[14/01/2015] Original
[23/04/2016] Changed download links to MiScion Pty Ltd Web Store

No comments: