This post is completed by 1 user

  • 0
Add to List

VBA-Excel: Read XML by Looping through Nodes

In our earlier post we have seen How to read data from XML file. In this post we will extend it further and loop through xml and print all the child nodes value under each parent node.

To Read Data from XML File using in Microsoft Excel, you need to follow the steps below:

  • Create the object of “Microsoft XML Parser” ) (Microsoft.XMLDOM is the COM object of Microsoft XML Parser)
  • Load the XML from a specified path.
  • Select the tag from the XML file using SelectNodes or SelectSingleNode.
  • SelectNodes – Selects a list of nodes matches the Xpath pattern.
  • Loop through all the nodes and for each nodes get the child nodes, and  then loop through child nodes and print them.

Sample XML: (Sample File has been taken from- https://msdn.microsoft.com/en-us/library/ms762271%28v=vs.85%29.aspx )

Read XML -1

Read XML -1

  • Create the object of “Microsoft XML Parser” ) (Microsoft.XMLDOM is the COM object of Microsoft XML Parser)

Set oXMLFile = CreateObject("Microsoft.XMLDOM")

  • Load the XML from a specified path.

               XMLFileName = "D:\Sample.xml"    

               oXMLFile.Load (XMLFileName)

  • Select the tag from the XML file using SelectNodes or SelectSingleNode.

             SelectNodes – Selects a list of nodes matches the Xpath pattern.

             Set Books = oXMLFile.SelectNodes("/catalog/book")

  • Loop through all the nodes and for each nodes get the child nodes, and  then loop through child nodes and print them.
    For i = 0 To Books.Length - 1
        For j = 0 To Books(i).ChildNodes.Length - 1
           Books(i).ChildNodes(j).tagname
           Books(i).ChildNodes(j).Text
        Next
    Next

NOTE:

Reference neededHow to add “Microsoft Forms 2.0 Object Library”
Microsoft Office 12.0 Object Library

Complete Code:

Output

Thanks Dontke for suggesting me this article.



Also Read:

  1. VBA-Excel: Format already written text in a word document – Format All Content
  2. Excel-VBA : Send a Simple Mail From MS Outlook Using Excel
  3. VBA-Excel: Add/Insert a Image/Picture in Word Document
  4. VBA-Excel: Update XML File
  5. Excel-VBA : Change Passwords for all the WorkSheets in one shot