Excel to XML Converter – Vba Code – Microsoft Dom

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.