Click here to Skip to main content
11,638,881 members (69,258 online)
Click here to Skip to main content

Export Excel to XML in VBA

, 3 May 2004 CPOL 253K 17K 49
Rate this:
Please Sign up or sign in to vote.
VBA to generate a clean XML file from an Excel table provided with nested nodes support
Sample Image - xls2xml.jpg

Introduction

In Excel XP, there is a new export option for XML. However, what I get is many rubbish tags (I'm so disappointed...). Then, after searching on the Google (our nice search engine) for some time about "Converting Excel to XML", what I 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. Smile | :)

Using the Code

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

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

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

Both functions have the same parameters. The 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 thing that is worth noticing is that we can use the node name to define 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 equivalent to <data><field1>....</field1><data>, /student/name/surname is equivalent 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 equivalent 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 have to be placed inside the same sub-node should be placed next to each other. For example, if /student/id and /student/name are placed 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 checks 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 are any bugs.

History

  • 4 May 2004 -- First release 0.8 version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Raymond Pang
Web Developer
Hong Kong Hong Kong
I am fond of programming especially in C++ (but unluckily I know many other different lang.). My special interest is on Graphics and image processing.

After graduated from Chinese University of Hong Kong with a research degree about graphics, I starts my career on web programming and games. Please Visit http://www.raybase.com which has software and game projects done by me.

You may also be interested in...

Comments and Discussions

 
SuggestionRecognise Attributes? Pin
ajingar1-Jun-15 23:55
memberajingar1-Jun-15 23:55 
QuestionDublin Core Pin
Member 115880958-Apr-15 2:33
memberMember 115880958-Apr-15 2:33 
Questionis it possible if i want to push a schema to this module? tia . Pin
Member 1146409120-Feb-15 2:09
memberMember 1146409120-Feb-15 2:09 
QuestionRoot Node Pin
Member 1108865717-Sep-14 0:22
memberMember 1108865717-Sep-14 0:22 
Questionfor merged cells Pin
Member 969485011-Aug-14 3:20
memberMember 969485011-Aug-14 3:20 
QuestionNot working in Excel 2011 mac Pin
Bharath L Narasimman8-Jul-14 1:46
memberBharath L Narasimman8-Jul-14 1:46 
GeneralMy vote of 5 Pin
Member 1082337716-May-14 2:49
memberMember 1082337716-May-14 2:49 
QuestionExport excel to XML Pin
kothai p6-Mar-14 1:34
memberkothai p6-Mar-14 1:34 
QuestionFormat of the hierarchy of data in the xml file Pin
Phil Gilbert20-Jun-13 23:52
memberPhil Gilbert20-Jun-13 23:52 
QuestionThanks! Great script! Pin
Vladislav Gasan3-Jan-13 0:35
memberVladislav Gasan3-Jan-13 0:35 
QuestionNeed vba code for xml to excel Pin
syed mubeen ahmed23-Jun-12 9:59
membersyed mubeen ahmed23-Jun-12 9:59 
Questionxml to excel Pin
syed mubeen ahmed23-Jun-12 9:47
membersyed mubeen ahmed23-Jun-12 9:47 
QuestionNeed help creating repeating group nodes using this tool Pin
Dan Gruber1-Sep-11 5:12
memberDan Gruber1-Sep-11 5:12 
Questionloop through all worksheets in a workbook t Pin
ushasharma198425-Jul-11 0:08
memberushasharma198425-Jul-11 0:08 
GeneralStill helpful in 2011 Pin
Eelze13-Apr-11 23:28
memberEelze13-Apr-11 23:28 
GeneralEXCEL HELP!!!! Pin
MRMIG14-Jul-10 20:09
memberMRMIG14-Jul-10 20:09 
GeneralThanks Pin
ajalilqarshi6-May-09 23:36
memberajalilqarshi6-May-09 23:36 
QuestionChange the default value for rootNodeName [modified] Pin
tasmaniac28-Apr-08 13:46
membertasmaniac28-Apr-08 13:46 
GeneralHelp with VB Pin
dommydee@hotmail.com19-Oct-07 2:36
memberdommydee@hotmail.com19-Oct-07 2:36 
Questionxml conforming to DTD Pin
meghz8-May-07 0:09
membermeghz8-May-07 0:09 
Generalgeneralized conversion to XML Pin
rkapl7-Jan-07 10:35
memberrkapl7-Jan-07 10:35 
GeneralNot support xsd Pin
PerlDev6-Oct-06 2:39
memberPerlDev6-Oct-06 2:39 
Generalexcel project deployment Pin
fatih isikhan11-Apr-06 3:59
memberfatih isikhan11-Apr-06 3:59 
GeneralRe: excel project deployment Pin
PerlDev6-Oct-06 2:34
memberPerlDev6-Oct-06 2:34 
GeneralRe: excel project deployment Pin
fatih isikhan6-Oct-06 3:21
memberfatih isikhan6-Oct-06 3:21 
GeneralCompile Error Pin
slefever1-Dec-05 8:07
memberslefever1-Dec-05 8:07 
GeneralRe: Compile Error Pin
Raymond Pang6-Dec-05 5:10
memberRaymond Pang6-Dec-05 5:10 
GeneralRe: Compile Error Pin
David Daltonhurst1-Jul-09 9:30
memberDavid Daltonhurst1-Jul-09 9:30 
Generalexport txt to xls Pin
Aunalisiraj12-Oct-05 22:07
memberAunalisiraj12-Oct-05 22:07 
QuestionAttributes? Pin
Mani Shankar25-Aug-05 23:42
memberMani Shankar25-Aug-05 23:42 
AnswerRe: Attributes? Pin
Raymond Pang27-Aug-05 21:20
memberRaymond Pang27-Aug-05 21:20 
GeneralAttributes! Pin
Mani Shankar29-Aug-05 0:33
memberMani Shankar29-Aug-05 0:33 
GeneralRe: Attributes! Pin
thaylin7928-Nov-06 4:40
memberthaylin7928-Nov-06 4:40 
AnswerRe: Attributes? Pin
kevingreiner10-Jan-06 2:02
memberkevingreiner10-Jan-06 2:02 
GeneralRe: Attributes? Pin
KevinGreiner25611-Aug-11 11:28
memberKevinGreiner25611-Aug-11 11:28 
GeneralRe: Attributes? Pin
Member 87030618-Mar-12 8:52
memberMember 87030618-Mar-12 8:52 
Generalgood explanation. Pin
R.Poonkothai10-Aug-05 13:50
sussR.Poonkothai10-Aug-05 13:50 
GeneralGood Job... Thanks!!! Pin
technoguys18-Feb-05 15:59
membertechnoguys18-Feb-05 15:59 
GeneralRe: Good Job... Thanks!!! Pin
Raymond Pang20-Feb-05 17:49
memberRaymond Pang20-Feb-05 17:49 
GeneralThanks Pin
banka_ravi21-Jan-05 0:24
memberbanka_ravi21-Jan-05 0:24 
GeneralVBA of Excel Pin
Willian.BR5-May-04 9:55
memberWillian.BR5-May-04 9:55 
GeneralRe: VBA of Excel Pin
Raymond Pang5-May-04 14:46
memberRaymond Pang5-May-04 14:46 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.150728.1 | Last Updated 4 May 2004
Article Copyright 2004 by Raymond Pang
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid