VBA-Excel: Read Data from XML File

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.

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

o   SelectSingleNode – Selects the first XMLNode that matches the pattern.

  • Iterate through all the Node by using Nodes.length and NodeValue.
  • Read the attributes by using Attribute.Length and getAttribute.
  • Read the particular index value from the XML File
  • Get all the values of particular type of nodes.

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 TitleNodes = oXMLFile.SelectNodes(“/catalog/book/title/text()”)

             SelectSingleNode – Selects the first XMLNode that matches the pattern.

             Set Nodes_Particular = oXMLFile.SelectSingleNode(“/catalog/book[4]/title/text()”)

  • Iterate through all the Node by using Nodes.length and NodeValue.

For i = 0 To (TitleNodes.Length – 1)

Title = TitleNodes(i).NodeValue

  • Read the attributes by using Attribute.Length and getAttribute.

Set Nodes_Attribute = oXMLFile.SelectNodes(“/catalog/book”)

For i = 0 To (Nodes_Attribute.Length – 1)

Attributes = Nodes_Attribute(i).getAttribute(“id”)

  • Read the particular index value from the XML File

oXMLFile.SelectSingleNode(“/catalog/book[4]/title/text()”)

  • Get all the values of particular type of nodes.

oXMLFile.SelectNodes(“/catalog/book/title[../genre = ‘Fantasy’]/text()”)

NOTE:

Reference needed:

How to add “Microsoft Forms 2.0 Object Library”
Microsoft Office 12.0 Object Library

Complete Code:

 

Read XML
Read XML

[sourcecode language=”VB”]
‘Get the 5th book title
Set Nodes_Particular = oXMLFile.SelectSingleNode("/catalog/book[4]/title/text()")’ index starts with 0, so put 4 for 5th book
MsgBox "5th Book Title : " & Nodes_Particular.NodeValue
[/sourcecode]

Get Book title

[sourcecode language=”VB”]
‘Get all the Fantasy books

Set Nodes_Fantasy = oXMLFile.SelectNodes("/catalog/book/title[../genre = ‘Fantasy’]/text()")
mainWorkBook.Sheets("Sheet3").Range("A1").Value = "Fantasy Books"

mainWorkBook.Sheets("Sheet3").Range("A1").Interior.ColorIndex = 40

mainWorkBook.Sheets("Sheet3").Range("A1").Borders.Value = 1

‘ get their titles

For i = 0 To (Nodes_Fantasy.Length – 1)

Title = Nodes_Fantasy(i).NodeValue

mainWorkBook.Sheets("Sheet3").Range("A" & i + 2).Value = Title

Next
[/sourcecode]

Read XML -2