CODEWITHSUNDEEP
excelvbagetelementsbytagnamemsxml2
vian
vian

Reputation: 11

VBA Excel MSXML2.XMLHTTP getelementsbytagname() not working

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

Upvotes: 0

Views: 11789

Answers (2)

QHarr
QHarr

Reputation: 84455

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

  1. Combine two XMLnodelist in VBA
  2. XmlDocument.GetElementsByTagName Method (String)
  3. VBA Web Services XML .responseXML parse
  4. Node.ownerDocument
  5. How to parse XML using vba
  6. The HTML DOM Element Object
  7. XmlHttpRequest – Http requests in Excel VBA

Upvotes: 2

SIM
SIM

Reputation: 22440

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

Upvotes: 3

Related Questions