VBA highly persisting run-time error 91

Question

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

The code is

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

and the error message is:

Run-time error '91':

Object variable or With block variable not set

and the compiler breaks the procedure in this line

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

Also the References Microsoft Internet Controls , Microsoft HTML Object Library and Microsoft XML, v6.0 are activated.

UPDATE

Question Answered in Entirety. Please read BOTH answers.

I've ckecked Stack numerous resources on this type of error but to no avail — ExoticBirdsMerchant, Feb 28 '14 at 21:04

score 2 · Accepted Answer · answered Feb 28 '14 at 23:16

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.

Try making this change:

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

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

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 highly persisting run-time error 91

1 Answers1

Linked