Excel MSXML2.XMLHTTP getelementsbytagname()不工作

kr98yfug  于 2023-10-22  发布在  其他
关注(0)|答案(2)|浏览(92)

这是我的代码

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

但它会警告此行的错误

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

我不知道怎么修,请帮帮忙!!!

62o28rlo

62o28rlo1#

请尝试以下代码。它应该获取你所需要的所有值。

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

或者,如果你想坚持.getElementsByTagName(),那么:

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

要添加到库中的引用:

Microsoft XML, v6.0
qacovj5a

qacovj5a2#

我认为您需要研究DOM模型与XML的关系。否则,您最终将不得不尝试沿着topics(0).OwnerDocument.DocumentElement.outerText的行从OwnerDocument沿着提取信息
我是新来的,所以我愿意接受反馈,但以下是我会采取的路线。
@Vityata在这里有一个很好的节点选择示例:Select a single node XML object using VBA
这里有一个例子,让你开始提取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

参考文献(没有特定的顺序-这是我快速浏览以获得导航的内容):

  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

相关问题