VBA XML Parser – Read XML Nodes & Import To Excel

Read XML using Excel VBA

XML is the file format that is widely used to transfer data over internet or between 2 systems with different platforms. The most widely used & familiar xml file in the internet world is the Sitemap.xml. This file has the major links to a website.

Other widely used file formats for data transfer are JSON, CSV. In this article, we are going to learn how to read the xml file using XML DOM (Data Object Model).

Excel VBA XML Parser

Using this tutorial you can build a XML parser using Excel VBA. Lets start with this step by step procedure. Open an Excel Workbook & Open VB Editor by pressing Alt + F11. Then follow these important steps.

1. Add reference to “Microsoft XML, V6.0” from Excel VB editor.
   • VB Editor -> Menu->Tools -> Reference
   • Scroll down till Microsoft XML, V2.0 or 3.0 or 6.0 appears. The version of XML depends on the OS & Office version installed in your machine.
   • Click Ok.
2. Now, Copy paste the code to your VBE.
3. Download a file from Internet or if you have a file already, Modify the xml file path in the code.
4. Run the code by pressing F5.

'--------------------------------------------------------------------------------
'Code by author@officetricks.com
'Visit https://officetricks.com to get more Free & Fully Functional VBA Codes
'--------------------------------------------------------------------------------
Public Sub Xml_To_Excel()
    Dim myURL As String, sFileNamePath As String, dsh As Worksheet, osh As Worksheet
    Dim WinHttpReq As Object, Node As IXMLDOMNode
    Dim xDoc As MSXML2.DOMDocument
    Dim list As MSXML2.IXMLDOMNodeList
    
    'Create XML DOM Object
    Set xDoc = New MSXML2.DOMDocument
    Set osh = ThisWorkbook.Sheets("Sheet2")
    oRow = 1
    
    'This is only a sample xml file - Change the File path to your Xml file path
    fname = "http://www.xmlfiles.com/examples/simple.xml"
    
    'Load Xml file to Object & Process Each node.
    If xDoc.Load(fname) Then
        Set list = xDoc.SelectNodes("//breakfast-menu/food")
        loopCount = 0
        Application.Wait DateAdd("s", 5, Now)
        DoEvents
        For Each Node In list
            oRow = oRow + 1
            '***Note: node names are Casesensitive***
            osh.Range("A" & oRow) = Node.SelectSingleNode("name").Text
            osh.Range("B" & oRow) = Node.Text
        Next
    Else
        MsgBox "Error Occured"
    End If
    
    MsgBox "Process Completed"
End Sub

This code uses XML DOM model to parse each node from input xml file. Then write it to the Excel file one by one.