Parsing Through XML File Excel VBA objDOM not Working

Question

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.

you need to add namespace E.g. stackoverflow.com/questions/58565060/… — QHarr
– QHarr, Commented Nov 22, 2019 at 22:24
Just tried that and sadly it's still doing the same thing and saying the length of the list is still zero — nboz75
– nboz75, Commented Nov 22, 2019 at 22:37
Hi, I will have a look. Also, it should be sub not a function as you don't return anything. — QHarr
– QHarr, Commented Nov 22, 2019 at 22:42
It's the format of the links - the encoding. Likely the '&' in the href of the anchor needs to be replaced with an entity reference as in & — QHarr
– QHarr, Commented Nov 22, 2019 at 22:54
As well as the entity encoding issue, the XPath query in the selectNodes call is incorrect — barrowc
– barrowc, Commented Nov 22, 2019 at 22:56

barrowc · Accepted Answer · 2019-11-22 23:11:26Z

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

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>

Thanks so much. It's working now. Couldn't have figured this out on my own. Thanks to QHarr also.

Collectives™ on Stack Overflow

Parsing Through XML File Excel VBA objDOM not Working

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related