#25 How to Parse XML Files with VBA Introduction

Sub ReadBookDetailsFromXML()
Dim xmlDoc As Object
Dim xmlNode As Object
Dim i As Integer
' Create a new XML Document object
Set xmlDoc = CreateObject("MSXML2.DOMDocument.6.0")
' Configure properties
xmlDoc.async = False
xmlDoc.validateOnParse = False
' Load the XML file
If Not xmlDoc.Load("C:\XML Files\books.xml") Then
MsgBox "Failed to load XML file. Exiting."
Exit Sub
End If
i = 2
For Each xmlNode In xmlDoc.DocumentElement.ChildNodes
Sheet2.Cells(i, 1).Value = xmlNode.getAttribute("id")
Sheet2.Cells(i, 2).Value = xmlNode.getElementsByTagName("author")(0).Text
Sheet2.Cells(i, 3).Value = xmlNode.getElementsByTagName("title")(0).Text
Sheet2.Cells(i, 4).Value = xmlNode.getElementsByTagName("genre")(0).Text
Sheet2.Cells(i, 5).Value = xmlNode.getElementsByTagName("price")(0).Text
Sheet2.Cells(i, 6).Value = xmlNode.getElementsByTagName("publish_date")(0).Text
Sheet2.Cells(i, 7).Value = xmlNode.getElementsByTagName("description")(0).Text
i = i + 1
Next xmlNode
' Release the XML Document object
Set xmlDoc = Nothing
End Sub
Sub ReadUniversityDetailsFromXML()
Dim xmlDoc As Object
Dim xmlFaculty As Object
Dim xmlProfessor As Object
Dim i As Integer
' Create a new XML Document object
Set xmlDoc = CreateObject("MSXML2.DOMDocument.6.0")
' Configure properties
xmlDoc.async = False
xmlDoc.validateOnParse = False
' Load the XML file
If Not xmlDoc.Load("C:\XML Files\university.xml") Then
MsgBox "Failed to load XML file. Exiting."
Exit Sub
End If
i = 2
'Loop through each faculty
For Each xmlFaculty In xmlDoc.DocumentElement.ChildNodes
'Loop through each professor
For Each xmlProfessor In xmlFaculty.ChildNodes
Sheets("Sheet3").Cells(i, 1).Value = xmlDoc.DocumentElement.getAttribute("name") ' University name
Sheets("Sheet3").Cells(i, 2).Value = xmlFaculty.getAttribute("name") ' Faculty name
Sheets("Sheet3").Cells(i, 3).Value = xmlProfessor.getAttribute("id") ' Professor ID
Sheets("Sheet3").Cells(i, 4).Value = xmlProfessor.getElementsByTagName("name")(0).Text ' Professor name
Sheets("Sheet3").Cells(i, 5).Value = xmlProfessor.getElementsByTagName("subject")(0).Text ' Professor subject
i = i + 1
Next xmlProfessor
Next xmlFaculty
Set xmlDoc = Nothing
End Sub

[@lovet7239]

Huge thanks to you! It was very useful for me. I have understood how to create XML parser i need!

[@troyajohnson26]

I've modified your code to match my xml data, but when I run the code, it highlights yellow the first item in the loop. How does it know to look for my specific child nodes? My xml is much larger and has many tables associated with it, but I only need the data from one particular section.

[@Mindful_Explorer]

It sounds like the code is highlighting because it's trying to loop through all child nodes, but it needs to be more specific for your larger XML. To focus on one section, you'll want to target the exact node you're interested in. Here's an example: Dim xmlDoc As Object Set xmlDoc = CreateObject("MSXML2.DOMDocument") xmlDoc.Load (filePath) ' Adjust "SectionName" to match the part of your XML you need Dim sectionNode As Object Set sectionNode = xmlDoc.SelectSingleNode("//SectionName") If Not sectionNode Is Nothing Then Dim childNode As Object For Each childNode In sectionNode.ChildNodes Debug.Print childNode.Text Next childNode Else MsgBox "Section not found" End If Make sure to check your XML structure to find the right node. If your XML is more complex, adjust the SelectSingleNode with the correct path.

[@troyajohnson26]

@@Mindful_Explorer Thanks! Still can't get the code to parse the data. my xml has much more data. It's a series of tables that include stuff like: . So trying to mirror yours is very confusing for me lol

[@lion123145678]

please share me XML file in the video