Get VBA Code to Convert Excel to Xml
Vba code in this page reads table data by each row and adds it to Xml dom element.
Once all the cell values are added, it is then saved as Xml file.
With this code you can also add Namespace to the root element & export Excel to xml data.
Note: Add Reference to Microsoft XML v3.0 or v6.0 from Vba Editor.
Sub create_xml_from_excel() Dim xDom As MSXML2.DOMDocument30 Dim xRoot As MSXML2.IXMLDOMElement Dim xRow As MSXML2.IXMLDOMElement Dim xNode As MSXML2.IXMLDOMElement Dim iSh As Worksheet Set iSh = ThisWorkbook.Sheets(1) Set xDom = New MSXML2.DOMDocument30 Set xRoot = xDom.createElement("root") Dim iR As Double Dim iC As Double Dim colHdr As String Dim colVal As String iR = 2 While iSh.Cells(iR, 1) <> "" iC = 1 Set xRow = xDom.createElement("Rowdata") While iSh.Cells(1, iC) <> "" colHdr = iSh.Cells(1, iC) colVal = iSh.Cells(iR, iC) Set xNode = xDom.createElement(colHdr) xNode.Text = colVal xRow.appendChild xNode iC = iC + 1 Wend xRoot.appendChild xRow iR = iR + 1 Wend xDom.appendChild xRoot xDom.DocumentElement.setAttribute "xmlns:tst", "Test xml with Namespace" xDom.Save ThisWorkbook.Path & "\Testing.xml" End Sub
Once this code is executed, You can find the xml file in the output path. But, it will be linear text format.
Excel to Xml conversion
In case you would like to view the file in beautified format, use this website -> xml beautify.
Even there are other ways to read the Excel data & convert it using basic file read write operations. But using Xml Dom is easy to manage, Add child nodes, Create nodes, append nodes, assign any attributes.
So, there is more advantage in using the Dom object as mentioned in the above code.
Xml means – Extensible Markup Language which is used to transfer files through internet. You can also notice that HTML is also a Markup language file.
This kind of files will have a start end end tag to hold the data between them.