VBA Default Namespace Bug

Question

I am trying to instantiate the Root node of a Dom document. However i am naming it xbrl and this name is in the default namespace which is xmlns="http://www.xbrl.org/2003/instance"

According to a previous post-answer MSXML is buggy (answer of barrowc) when it comes to default namespaces. So i had to make some modification to my code. These where

objXMLDoc.LoadXML (objXMLHTTP.responseText)

replaced by

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

AND ALSO

Dim objXMLHTTP As New MSXML2.XMLHTTP
Dim objXMLDoc As New MSXML2.DOMDocument

replace by

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

The number 60 symbolizes the version 6.0

So when i made these modifications the macro worked without an error. But now it works only sometimes. When it doesn't it gives me a

Run-time error -2147467259(80004005)':

Reference to undeclared namespace prefix:'us-gaap.'

I cannot understand the reason the macro crashes and believe it is a bug.

Can you help?

For reasons of completeness the entire macro is submitted below

Sub READSITE()

    Dim IE As InternetExplorer
    Dim els, el, colDocLinks As New Collection
    Dim lnk, res
    Dim Ticker As String
    Dim colXMLPaths As New Collection
    Dim XMLElement As String

    Set IE = New InternetExplorer

    IE.Visible = False

    Ticker = Worksheets("Sheet1").Range("A1").Value

    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
            End If
        Next el
    Next lnk

    XMLElement = Range("C1").Value

    'For each link, open the URL and display the Debt Instrument Insterest Rate
    For Each lnk In colXMLPaths
        res = GetData(CStr(lnk), XMLElement)
        With Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
            .NumberFormat = "@"
            .Value = Ticker
            .Offset(0, 1).Value = lnk
            .Offset(0, 2).Value = res
        End With
    Next lnk

End Sub

Function GetData(sURL As String, sXMLElement As String)
    Dim strXMLSite As String
    Dim objXMLHTTP As New MSXML2.XMLHTTP60
    Dim objXMLDoc As New MSXML2.DOMDocument60
    Dim objXMLNodexbrl As MSXML2.IXMLDOMNode
    Dim objXMLNodeElement As MSXML2.IXMLDOMNode
    Dim objXMLNodeStkhldEq As MSXML2.IXMLDOMNode

    GetData = "?" 'No data from XML
    objXMLHTTP.Open "GET", sURL, False  '<<EDIT: GET the site
    objXMLHTTP.send
    objXMLDoc.LoadXML (objXMLHTTP.responseText)
    objXMLDoc.setProperty "SelectionNamespaces", "xmlns:r='http://www.xbrl.org/2003/instance'"

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

    Set objXMLNodeElement = objXMLNodexbrl.SelectSingleNode(sXMLElement)

    If Not objXMLNodeElement Is Nothing Then
        GetData = objXMLNodeElement.Text
    End If
End Function

Sub LoadPage(IE As Object, url As String)
    IE.Navigate url
    Do While IE.Busy Or IE.ReadyState <> READYSTATE_COMPLETE
        DoEvents
    Loop
End Sub

---

It is also a strange and frustrating note, to see that if i alter my macro in the before-corrections status according to the modifications given to me by barrowc i can now see that the macro works!

Answer 1

I was able to terminate the bug swiftly by checking in Diff Checker the vast code with previous code samples i had. It seems all you need is to remove the 60 from these lines (god knows why...)

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