Excel XML – How to Export XML Data From Excel

XML is a worldwide supported format, You can import xml data in almost any program available. In this post i will demonstrate how you can export xml data from table, Lets start.

What do you need?

To export xml data from excel you will need two things 1. A Data Table 2. XML Map. I am sure i dont need to explain what is data table, but i would like to explain what is XML map.

What is XML Map?

A XML Map is a .xml file which have some xml code matching with the data table structure in excel.

How it works?

You need to create a XML file that have xml code matching with teh structured of excel data table. After that, you need to add that xml map in excel and assign it to excel table. once it is mapped on excel table, just right click on table and hit export. it will ask a save file name, you provide a name and TADA!!

How to create XML Map file?

Super simple, Let’s say you have four columns in your data table such as “Name”,”Address”,”Zip”,”Phone”. In XML file you need to create atleast two record for these columns. this is how the xml file will look. you can just type this code in text file and save as “Map.xml”. file name can be anything, i am just saying it “Map.xml”

Export XML

How to Map it on excel table?

In Excel, Go to Developer Tab, In the XML tab, click on Source tab, it will open a side pane in excel. in that pane you will see XML Maps button, click this button and browse “Map.xml” file you create above.

Map XML in Excel

And thats it!! Now, you can right click on tabel and hit XML and click export. it will export all records in XML file.

But wait!! isn’t it too much manual work, Let’s automate this process.

Yes, you can automate this entire process, first of all you need to create an XML Map. I created a function where you can pass the range and it will return XML code

Function GetHTML(rngRange As Range)
      Dim varData
      Dim lngR As Long
      Dim strMap As String
      Dim lngC As Long
      varData = rngRange.Resize(3)
      strMap = “” & vbNewLine & “”
      For lngR = LBound(varData) + 1 To UBound(varData)
            strMap = strMap & vbNewLine & “”
            For lngC = LBound(varData) To UBound(varData, 2)
                  strMap = strMap & vbNewLine & vbTab & “<” & varData(1, lngC) & “>” & varData(lngR, lngC) & “
            Next lngC
      strMap = strMap & vbNewLine & “”
      Next lngR
      strMap = strMap & vbNewLine & “”
      GetHTML = strMap
End Function

How to create XML file?

Dim strXMLFile as string
strXMLFile =”YourPath\Map.xml”
CreateObject(“Scripting.FileSystemobject”).createtextfile(strXMLFile =”YourPath\Map.xml”).write GetHTML(rngToExport)

How to add XML map programmatically?

Dim objMap As XmlMap
Set objMap = ThisWorkbook.XmlMaps.Add(strTemp, “Data”)

How to Map XML map on table?

Set objList = Sheet1.ListObjects.Add(xlSrcRange, rngToExport)
For Each objListCol In objList.ListColumns
      objListCol.XPath.SetValue objMap, “/Data/Record/” & objListCol.Range.Cells(1).Value
Next objListCol

How to Export XML table?

Dim strExportXML as String
strExportXml =”YourPath\Output.xml”
objMap.Export strExportXml, True

Here you can download a working file

ExportDataAsXML.xlsm (11 downloads)

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *