Export Excel to XML in VBA

• Download source files - 3.33 Kb
• Download demo project - 47.4 Kb

Introduction

In Excel XP, there is a new export option for XML, however, what I get is many many rubblish tags ( so disappointed..) . Then, after searching on the google (our nice search engine) for sometime about "Converting Excel to XML", what I can get are conversion tools that simply make a Rational table to XML. However, what I want is the support of nested structures (e.g. <data><field1>....</field1><data>). As a result, I decided to write my own using VBA .. ( don't ask me why VBA.. may be I will port it to my favorite C++ platform later :)

Using the code

The source codes contains 2 functions GenerateXMLDOM() and fGenerateXML(). They are actually doing the same thing in different approach,

GenerateXMLDOM -- Give a MS XMLDOM object which you can play with or serialize into an XML at any time using "Save" method.

fGenerateXML -- Generate a long string which is ready for output as file (more readable, as proper newline is added)

Both functions has the same parameters , First one is (rngData) , which is the selected table area in the Excel Sheet for conversion. The Second (rootNodeName) is the first/root node's name used in the XML.

In the selected range, the first row is expected to be the field (or node) names. One worth to notice is we can use the node name to defines the level it belongs to. Started and Separated by the node delimiter "/", the node name is one level deeper than the previous one. e.g. /data/field1 is equvalent to <data><field1>....</field1><data> , /student/name/surname is equvalent to <student><name><surname>.... </surname></name></student>

'

' rngData : The selected region on excel sheet, with the first row as field name, and data rows below

'                       For the field name, use the node delimiter "/" to build the hierarchy of data

'                       e.g. /data/field1 is equvalent to <DATA><FIELD1>....</FIELD1><DATA>

'

' rootNodeName : The xml document root node tag name


Function GenerateXMLDOM(rngData As Range, rootNodeName As String)
...


Function fGenerateXML(rngData As Range, rootNodeName As String) As String
...

Limitation and Notes

The ordering of fields may affect the generated XML, fields that has to place inside the same sub-node should be place next to each other. For example, if /student/id and /student/name are place next to each other, it will generate

<student><id>..</id><name>...</name></student>

however, if not, the result will be

<student><id>..</id></student> <somebrabra...> ... </somebrabra...> <student><name>..</name></student>

The reason is that it only check with the last field instead of all before deciding where the node should be created.

Finally I would like to thank Hasler Thomas , the author of A Filebrowser for VBA , who provided the code for file browse. Hope this code will be useful for you, please let me know if there is any bugs.

History

4 May 2004 -- First release 0.8 version