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!!!

Answer 1

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