Be the first user to complete this post Login To Mark Completed

0

Add to List Add To List..(Max 5) Close Save

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 )

• 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:

'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

'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

---

---

Also Read:

1. Excel-VBA : Prevent Adding New Worksheet
2. Excel-VBA : Prevent Changing the WorkSheet Name
3. VBA-Excel: Consolidator – Merge or Combine Multiple Excel Files Into One
4. VBA-Excel: Format already written text in a word document – Format Paragraphs
5. VBA-Excel: Working with Bookmarks- Insert text before Bookmark

---