#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