CODEWITHSUNDEEP
excelvba
nboz75
nboz75

Reputation: 55

Parsing Through XML File Excel VBA objDOM not Working

Trying to parse thorugh an XML file with VBA and print the results to an Excel spreadsheet. But whenever I get to creating a list with the following code it doesn't create one and I can't figure out why.

Set Songs = objDOM.SelectNodes("/channel/item")

Full code and XML is below

Function fnReadXMLByTags()
    'Code From: https://excel-macro.tutorialhorizon.com/vba-excel-read-xml-by-looping-through-nodes/
    'And Here: https://desmondoshiwambo.wordpress.com/2012/07/03/how-to-load-xml-from-a-local-file-with-msxml2-domdocument-6-0-and-loadxml-using-vba/
    Dim mainWorkBook As Workbook
    Set mainWorkBook = ActiveWorkbook
    mainWorkBook.Sheets("XML_Parser").Range("A:C").Clear


    Dim intFile As Integer
    Dim strTemp As String
    Dim strXML As String
    Dim strOrderText As String
    Dim objDOM As Object
    Dim Songs As Object


    XMLFileName = ThisWorkbook.Path & "\PlaylistFeed.xml"


    'Open file
    intFile = FreeFile
    Open XMLFileName For Input As intFile
    strXML = ""
    'Load XML into string strXML
    While Not EOF(intFile)
        Line Input #intFile, strTemp
        strXML = strXML & strTemp
    Wend
    Close intFile

    'Load the XML into DOMDocument object
    Set objDOM = CreateObject("MSXML2.DOMDocument.6.0")
    objDOM.LoadXML strXML

    mainWorkBook.Sheets("XML_Parser").Range("A1,B1,C1,D1").Interior.ColorIndex = 40
    mainWorkBook.Sheets("XML_Parser").Range("A1,B1,C1,D1").Borders.Value = 1
    mainWorkBook.Sheets("XML_Parser").Range("A" & 1).Value = "Song Number"
    mainWorkBook.Sheets("XML_Parser").Range("B" & 1).Value = "Tag Number"
    mainWorkBook.Sheets("XML_Parser").Range("C" & 1).Value = "Item Node"
    mainWorkBook.Sheets("XML_Parser").Range("D" & 1).Value = "Value"
    Set Songs = objDOM.SelectNodes("/channel/item")
    mainWorkBook.Sheets("XML_Parser").Range("A" & 2).Value = Songs.Length 'always says 0
    intCounter = 2
    For i = 0 To Songs.Length - 1
        For j = 0 To Songs(i).ChildNodes.Length - 1
           mainWorkBook.Sheets("XML_Parser").Range("A" & intCounter).Value = i + 1
           mainWorkBook.Sheets("XML_Parser").Range("B" & intCounter).Value = j + 1
           mainWorkBook.Sheets("XML_Parser").Range("C" & intCounter).Value = Songs(i).ChildNodes(j).tagname
           mainWorkBook.Sheets("XML_Parser").Range("D" & intCounter).Value = Songs(i).ChildNodes(j).Text
           mainWorkBook.Sheets("XML_Parser").Range("A" & intCounter).Borders.Value = 1
           mainWorkBook.Sheets("XML_Parser").Range("B" & intCounter).Borders.Value = 1
           mainWorkBook.Sheets("XML_Parser").Range("C" & intCounter).Borders.Value = 1
           mainWorkBook.Sheets("XML_Parser").Range("D" & intCounter).Borders.Value = 1

           intCounter = intCounter + 1
           mainWorkBook.Sheets("XML_Parser").Range("A" & intCounter, "B" & intCounter, "C" & intCounter, "D" & intCounter).Interior.ColorIndex = 40
           mainWorkBook.Sheets("XML_Parser").Range("A" & intCounter, "B" & intCounter, "C" & intCounter, "D" & intCounter).Borders.Value = 1
           mainWorkBook.Sheets("XML_Parser").Range("A" & intCounter).Value = "Song Number"
           mainWorkBook.Sheets("XML_Parser").Range("B" & intCounter).Value = "Tag Number"
           mainWorkBook.Sheets("XML_Parser").Range("C" & intCounter).Value = "Item Node"
           mainWorkBook.Sheets("XML_Parser").Range("D" & intCounter).Value = "Value"
        Next
         intCounter = intCounter + 1
    Next




End Function

<rss xmlns:dc="http://purl.org/dc/elements/1.1/" version="2.0">
<channel>
<title>WTMD Playlist</title>
<link>http://www.wtmd.org</link>
<description>Recently Played Songs at WTMD</description>
<item>
<title>Son Volt - Drown</title>
<link>
http://www.amazon.com/exec/obidos/external-search?tag=wt897fmrafomu-20&index=digital-music-track&keyword=Son+Volt%2BDrown
</link>
<description>Album: Trace</description>
<pubDate>Mon, 03 Dec 2012 20:09:44 GMT</pubDate>
<guid>
http://www.amazon.com/exec/obidos/external-search?tag=wt897fmrafomu-20&index=digital-music-track&keyword=Son+Volt%2BDrown
</guid>
<dc:date>2012-12-03T20:09:44Z</dc:date>
</item>
<item>
<title>Allen Stone - Sleep</title>
<link>
http://www.amazon.com/exec/obidos/external-search?tag=wt897fmrafomu-20&index=digital-music-track&keyword=Allen+Stone%2BSleep
</link>
<description>Album: Allen Stone</description>
<pubDate>Mon, 03 Dec 2012 20:07:19 GMT</pubDate>
<guid>
http://www.amazon.com/exec/obidos/external-search?tag=wt897fmrafomu-20&index=digital-music-track&keyword=Allen+Stone%2BSleep
</guid>
<dc:date>2012-12-03T20:07:19Z</dc:date>
</item>

When testing the length the Songs list always comes at zero and I can't figure out why. Everything I've tried either has the same problem or gives an error.

Upvotes: 1

Views: 395

Answers (1)

barrowc
barrowc

Reputation: 10689

As QHarr pointed out, in parts of the XML document, you have text containing the & symbol (ampersand), for example:

tag=wt897fmrafomu-20&index=digital-music-track

The & symbol on its own is not allowed as part of well formed XML and needs to be replaced by the entity reference &amp;:

tag=wt897fmrafomu-20&amp;index=digital-music-track

The XPath query in the call to selectNodes is also incorrect. You have:

Set Songs = objDOM.SelectNodes("/channel/item")

That would only be correct if "channel" was the root node of the XML document. In fact, "rss" is the root node so you would need to use this instead:

Set Songs = objDOM.SelectNodes("/rss/channel/item")

The calls to set the color index and borders are incorrect. You should have this instead:

mainWorkBook.Sheets("XML_Parser").Range("A" & intCounter & ":D" & intCounter).Interior.ColorIndex = 40
mainWorkBook.Sheets("XML_Parser").Range("A" & intCounter & ":D" & intCounter).Borders.Value = 1

The XML document as shown in the question is incomplete so I'm assuming that the remaining tags were added to close the "channel" and "rss" tags. I'm also assuming that an XML declaration was added at the start:

<?xml version="1.0" encoding="utf-8"?>
<rss xmlns:dc="http://purl.org/dc/elements/1.1/" version="2.0">
<channel>
<title>WTMD Playlist</title>
<link>http://www.wtmd.org</link>
<description>Recently Played Songs at WTMD</description>
<item>
<title>Son Volt - Drown</title>
<link>
http://www.amazon.com/exec/obidos/external-search?tag=wt897fmrafomu-20&amp;index=digital-music-track&amp;keyword=Son+Volt%2BDrown
</link>
<description>Album: Trace</description>
<pubDate>Mon, 03 Dec 2012 20:09:44 GMT</pubDate>
<guid>
http://www.amazon.com/exec/obidos/external-search?tag=wt897fmrafomu-20&amp;index=digital-music-track&amp;keyword=Son+Volt%2BDrown
</guid>
<dc:date>2012-12-03T20:09:44Z</dc:date>
</item>
<item>
<title>Allen Stone - Sleep</title>
<link>
http://www.amazon.com/exec/obidos/external-search?tag=wt897fmrafomu-20&amp;index=digital-music-track&amp;keyword=Allen+Stone%2BSleep
</link>
<description>Album: Allen Stone</description>
<pubDate>Mon, 03 Dec 2012 20:07:19 GMT</pubDate>
<guid>
http://www.amazon.com/exec/obidos/external-search?tag=wt897fmrafomu-20&amp;index=digital-music-track&amp;keyword=Allen+Stone%2BSleep
</guid>
<dc:date>2012-12-03T20:07:19Z</dc:date>
</item>
</channel>
</rss>

Upvotes: 1

Related Questions