VBA Excel MSXML2.XMLHTTP getelementsbytagname() not working

Question

Here is my code

Sub loadrss()
    Dim http As Object, html As New HTMLDocument, topics As Object, titleElem As Object, topic As HTMLHtmlElement, i As Integer
    Set http = CreateObject("MSXML2.XMLHTTP")
    http.Open "GET", "http://antt.vn/rss/trang-chu.rss", False
    http.send
    html.body.innerHTML = http.responseText
    Set topics = html.getElementsByTagName("Item")
    i = 55
    For Each topic In topics
        Sheet7.Cells(i, 15).Value = topic.getElementsByTagName("title")(0).innerText
        Sheet7.Cells(i, 16).Value = topic.getElementsByTagName("link")(0).innerText
        Sheet7.Cells(i, 17).Value = topic.getElementsByTagName("pubDate")(0).innerText
        i = i + 1
    Next
End Sub

but it alert an error with this row

Sheet7.Cells(i, 15).Value = topic.getElementsByTagName("title")(0).innerText

I don't know how to fix it, please help!!!

Which error? What did you learn when you Googled the error code? What else have you tried so far to solve the issue? *"Not Working"* is not very descriptive. — ashleedawg, Feb 08 '18 at 04:51
Actually I can see a couple problems already; I'll give you a hint: spelling & accuracy is important! To help you find the problems, add this line `Option Explicit` as the first line of the module. It's a simple but important addition to [any] module when troubleshooting, especially while learning, and it will force you to properly declare and spell names of variables, objects, properties, etc. when you try to run the code. — ashleedawg, Feb 08 '18 at 04:57
"Runtime error '91': Object variable or with block variable not set" I think the problem is in "http://antt.vn/rss/trang-chu.rss", my code work with another website but this, with <![CDATA[]]> is a problem? i Think!!! I'm not a coder, not good english too, sory :D — vian, Feb 08 '18 at 04:59
More hints from Googling "[Runtime error '91': Object variable or with block variable not set (Click Here)](https://msdn.microsoft.com/en-us/library/aa264506(v=vs.60).aspx)" — ashleedawg, Feb 08 '18 at 05:04
Your feed is XML, not HTML. They're not really interchangeable. https://stackoverflow.com/questions/32232739/get-attribute-from-xml-rss — Tim Williams, Feb 08 '18 at 05:37

score 3 · Accepted Answer · answered Feb 08 '18 at 13:39

Try the following code. It should fetch you all the values you are after.

Sub XML_Parsing_ano()
    Dim http As New XMLHTTP60
    Dim xmldoc As Object, post As Object

    With http
        .Open "GET", "http://antt.vn/rss/trang-chu.rss", False
        .send
        Set xmldoc = CreateObject("MSXML2.DOMDocument")
        xmldoc.LoadXML .responseXML.XML
    End With

     For Each post In xmldoc.SelectNodes("//item")
        r = r + 1: Cells(r, 1) = post.SelectNodes(".//title")(0).Text
        Cells(r, 2) = post.SelectNodes(".//pubDate")(0).Text
        Cells(r, 3) = post.SelectNodes(".//link")(0).Text
    Next post
End Sub

Or, If you wanna stick to .getElementsByTagName() then:

Sub XML_Parsing_ano()
    Dim http As New XMLHTTP60
    Dim xmldoc As Object, post As Object

    With http
        .Open "GET", "http://antt.vn/rss/trang-chu.rss", False
        .send
        Set xmldoc = CreateObject("MSXML2.DOMDocument")
        xmldoc.LoadXML .responseXML.XML
    End With

     For Each post In xmldoc.getElementsByTagName("item")
        r = r + 1: Cells(r, 1) = post.getElementsByTagName("title")(0).Text
        Cells(r, 2) = post.getElementsByTagName("pubDate")(0).Text
        Cells(r, 3) = post.getElementsByTagName("link")(0).Text
    Next post
End Sub

Reference to add to the library:

Microsoft XML, v6.0

Hi QHarr!! You can follow this link to see an update: [Link](https://stackoverflow.com/questions/48682377/select-a-single-node-xml-object-using-vba/48691608#48691608) — SIM, Feb 08 '18 at 17:45
Hi @SIM, it work very well. Big thank to you. I see the difference is xmldoc.LoadXML .responseXML.XML vs html.body.innerHTML = http.responseText — vian, Feb 12 '18 at 01:23

QHarr · Answer 2 · 2018-02-08T10:58:05.243

I think you need to explore the DOM model in relation to XML. Otherwise you will end up having to try and extract information from the OwnerDocument along the lines of topics(0).OwnerDocument.DocumentElement.outerText

I am new to this so am open to feedback on this but the following is the route I would take.

There is a good example of node selection by @Vityata here: Select a single node XML object using VBA

Here is an example to get you started of extracting the pubDates.

Sub testing()

    Dim xmlhttp As XMLHTTP60

    Set xmlhttp = New MSXML2.ServerXMLHTTP60

    Dim objXML As MSXML2.DOMDocument60 'MSXML2.DOMDocument

    Set objXML = New MSXML2.DOMDocument60

    With xmlhttp
        .Open "GET", "http://antt.vn/rss/trang-chu.rss", False
        .Send
     Set objXML = .responseXML
    End With

    Dim elemList As IXMLDOMNodeList
    Dim elem As IXMLDOMNode
    Set elemList = objXML.GetElementsByTagName("pubDate")

    For Each elem In elemList

       Debug.Print elem.nodeTypedValue

    Next elem

End Sub

References (in no particular order - this is what I had a quick look at to get a handle on navigation):

VBA Excel MSXML2.XMLHTTP getelementsbytagname() not working

2 Answers2