ReadXML.vb

Sub ReadXML()
    Call fnReadXMLByTags
End Sub

Function fnReadXMLByTags()
    Dim mainWorkBook As Workbook
    Set mainWorkBook = ActiveWorkbook
    mainWorkBook.Sheets("Sheet1").Range("A:A").Clear
    Set oXMLFile = CreateObject("Microsoft.XMLDOM")
    XMLFileName = "D:\Sample.xml"
    oXMLFile.Load (XMLFileName)
    mainWorkBook.Sheets("Sheet1").Range("A1,B1,C1").Interior.ColorIndex = 40
    mainWorkBook.Sheets("Sheet1").Range("A1,B1, C1").Borders.Value = 1
    mainWorkBook.Sheets("Sheet1").Range("A" & 1).Value = "S.No"
    mainWorkBook.Sheets("Sheet1").Range("B" & 1).Value = "Book Node"
    mainWorkBook.Sheets("Sheet1").Range("C" & 1).Value = "Value"
    Set Books = oXMLFile.SelectNodes("/catalog/book")
    intCounter = 2
    For i = 0 To Books.Length - 1
        For j = 0 To Books(i).ChildNodes.Length - 1
           mainWorkBook.Sheets("Sheet1").Range("A" & intCounter).Value = j + 1
           mainWorkBook.Sheets("Sheet1").Range("B" & intCounter).Value = Books(i).ChildNodes(j).tagname
           mainWorkBook.Sheets("Sheet1").Range("C" & intCounter).Value = Books(i).ChildNodes(j).Text
           mainWorkBook.Sheets("Sheet1").Range("A" & intCounter).Borders.Value = 1
           mainWorkBook.Sheets("Sheet1").Range("B" & intCounter).Borders.Value = 1
           mainWorkBook.Sheets("Sheet1").Range("C" & intCounter).Borders.Value = 1
           intCounter = intCounter + 1
        Next
         intCounter = intCounter + 1
    Next
    
End Function