VBA高度持久的运行时错误91 [英] VBA highly persisting run-time error 91

问题描述

我遇到的问题是，当我尝试触发宏时，我经常遇到错误.

I am confronted by the problem that i am constantly getting an error when i try to fire my macro.

代码是

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

，错误消息是:

运行时错误"91":

对象变量或未设置块变量

编译器在此行中断了程序

and the compiler breaks the procedure in this line

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

清楚地设置对象时怎么办?这段代码从这里起源，在这里工作得非常好！

How is this possible when the object is clearly set? This code originates from here where it works absolutely fine!

还激活了引用 Microsoft Internet Controls ， Microsoft HTML Object Library 和 Microsoft XML v6.0 .

更新

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

推荐答案

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

The xbrl element is in the default namespace of the document (defined via xmlns="http://www.xbrl.org/2003/instance"). MSXML2 has issues with default namespaces.

尝试进行此更改:

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

Set objXMLNodexbrl = objXMLDoc.SelectSingleNode("r:xbrl")

还尝试将这两个声明更改为最新的MSXML2 v6.0对象:

Also try changing these two declarations to the up to date MSXML2 v6.0 objects:

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

这篇关于VBA高度持久的运行时错误91的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！