VBA 高度持久性运行时错误 91

Question

我遇到了一个问题，当我尝试触发我的宏时，我经常收到错误。

代码是

Sub MAGAZINE_iiii()
    Dim IE As InternetExplorer ' MODIFICATION
    Dim els, el, colDocLinks As New Collection
    Dim lnk
    Dim res
    Dim Ticker As String ' MODIFICATION
    Dim colXMLPaths As New Collection '<<<EDIT
    Set IE = New InternetExplorer 'MODIFICATION
    IE.Visible = True
    Ticker = Worksheets("Sheet1").Range("A1").Value 'MODIFICATION
    LoadPage IE, "https://www.sec.gov/cgi-bin/browse-edgar?" & _
                  "action=getcompany&CIK=" & Ticker & "&type=10-Q" & _
                  "&dateb=&owner=exclude&count=20"
    Set els = IE.document.getElementsByTagName("a")
    For Each el In els
        If Trim(el.innerText) = "Documents" Then
            colDocLinks.Add el.href
        End If
    Next el
    For Each lnk In colDocLinks
        LoadPage IE, CStr(lnk)
        For Each el In IE.document.getElementsByTagName("a")
            If el.href Like "*[0-9].xml" Then
                Debug.Print el.innerText, el.href
                colXMLPaths.Add el.href  '<<<EDIT
            End If
        Next el
    Next lnk
    For Each lnk In colXMLPaths ' EVERY ITERATION MUST BE FOR AN INSTANCE DOCUMENTS TO PARSE INSIDE THE LOOP
        Dim objXMLHTTP As New MSXML2.XMLHTTP
        Dim objXMLDoc As New MSXML2.DOMDocument
        Dim objXMLNodexbrl As MSXML2.IXMLDOMNode
        Dim objXMLNodeDIIRSP As MSXML2.IXMLDOMNode
        objXMLHTTP.Open "POST", CStr(lnk), False  '<<EDIT: GET not POST
        objXMLHTTP.send
        objXMLDoc.LoadXML (objXMLHTTP.responseText)
        Set objXMLNodexbrl = objXMLDoc.SelectSingleNode("xbrl")
        Set objXMLNodeDIIRSP = objXMLNodexbrl.SelectSingleNode("us-gaap:DebtInstrumentInterestRateStatedPercentage")
        Worksheets("Sheet1").Range("D1").Value = objXMLNodeDIIRSP.Text
        Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select
    Next lnk
End Sub
Sub LoadPage(IE As InternetExplorer, URL As String)
    IE.navigate URL
    Do While IE.Busy Or IE.readyState <> READYSTATE_COMPLETE
        DoEvents
    Loop
End Sub

错误消息是：

Run-time error '91':

Object variable or With block variable not set

编译器破坏了这一行中的过程

Set objXMLNodeDIIRSP = objXMLNodexbrl.SelectSingleNode("us-gaap:DebtInstrumentInterestRateStatedPercentage")

当对象被明确设置时，这怎么可能？这段代码起源于这里，它工作起来绝对可以！

此外，引用Microsoft Internet Controls、Microsoft HTML Object Library和Microsoft XML, v6.0也被激活。

更新

问题完整回答。请阅读两个答案。

Answer 1

xbrl 元素位于文档的默认命名空间中（通过 xmlns="http://www.xbrl.org/2003/instance" 定义）。MSXML2 存在默认命名空间问题。

尝试进行此更改：

objXMLDoc.LoadXML objXMLHTTP.responseText
objXMLDoc.setProperty "SelectionNamespaces", "xmlns:r='http://www.xbrl.org/2003/instance'"
Set objXMLNodexbrl = objXMLDoc.SelectSingleNode("r:xbrl")

此外，请尝试将这两个声明更改为最新的 MSXML2 v6.0 对象：

Dim objXMLHTTP As New MSXML2.XMLHTTP60
Dim objXMLDoc As New MSXML2.DOMDocument60