Show Buttons
Share On Facebook
Share On Twitter
Share On Google Plus
Share On Linkdin
Share On Reddit
Contact us
Hide Buttons

VBA-Excel: Read XML by Looping through Nodes

In our ear­lier post we have seen How to read data from XML file. In this post we will extend it fur­ther and loop through xml and print all the child nodes value under each par­ent node.

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

  • Cre­ate the object of “Microsoft XML Parser” ) (Microsoft.XMLDOM is the COM object of Microsoft XML Parser)
  • Load the XML from a spec­i­fied path.
  • Select the tag from the XML file using SelectN­odes or SelectS­in­gleN­ode.
  • SelectN­odes – 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.

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

Read XML -1

Read XML –1

  • Cre­ate the object of “Microsoft XML Parser” ) (Microsoft.XMLDOM is the COM object of Microsoft XML Parser)

Set oXML­File = CreateObject(“Microsoft.XMLDOM”)

  • Load the XML from a spec­i­fied path.

               XML­File­Name = “D:\Sample.xml”    

               oXMLFile.Load (XMLFileName)

  • Select the tag from the XML file using SelectN­odes or SelectS­in­gleN­ode.

             SelectN­odes – 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:

Ref­er­ence neededHow to add “Microsoft Forms 2.0 Object Library”
Microsoft Office 12.0 Object Library

Com­plete Code:

Output

Thanks Don­tke for sug­gest­ing me this article.

You may also like...

Leave a Reply

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

%d bloggers like this: