CODEWITHSUNDEEP
xmlvbaexcel
ExoticBirdsMerchant
ExoticBirdsMerchant

Reputation: 1516

VBA XML ChildNodes

I am trying to modify a fine piece of code that i got from user2140261 that pulls data from the XML type (XBRL-Template) SEC EDGAR Database. So since i want to pull element values from other Nodes as well, my first idea was to show the list with the Nodes of the XML instance in a MsgBox; by using the childNodes Property. The first little snippet, has the minor modification I made to avoid any confusion while in the second one we see the original code plus the 3 statements of the modifications

Dim Tiger As String  

Tiger = objXMLNodexbrl.ChildNodes(1)

MsgBox Tiger

Sub GetNode()
Dim strXMLSite As String
Dim objXMLHTTP As MSXML2.XMLHTTP
Dim objXMLDoc As MSXML2.DOMDocument
Dim objXMLNodexbrl As MSXML2.IXMLDOMNode
Dim objXMLNodeDIIRSP As MSXML2.IXMLDOMNode
Dim Tiger As String

Set objXMLHTTP = New MSXML2.XMLHTTP
Set objXMLDoc = New MSXML2.DOMDocument

strXMLSite = "http://www.sec.gov/Archives/edgar/data/10795/000119312513456802/bdx-20130930.xml"

objXMLHTTP.Open "POST", strXMLSite, False
objXMLHTTP.send
objXMLDoc.LoadXML (objXMLHTTP.responseText)

Set objXMLNodexbrl = objXMLDoc.SelectSingleNode("xbrl")

Tiger = objXMLNodexbrl.ChildNodes(1)

MsgBox Tiger

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

Worksheets("Sheet1").Range("A1").Value = objXMLNodeDIIRSP.Text
End Sub

So when i am trying to parse step-by-step the code using an F8 i get a 'Runtime Error 438' telling me that the object doesn't support this property or method.

enter image description here

@user2140261 wow you were so right...just one question i do change my code with

Tiger = objXMLNodexbrl.ChildNodes(1).Text

but i am getting the numerical value of the element instead of the number of the nodes. How is that possible? since ChildNodes Property : "Contains a node list containing the child nodes"...

enter image description here

Upvotes: 1

Views: 11902

Answers (1)

user2140261
user2140261

Reputation: 7993

Change your line of code from: 

Tiger = objXMLNodexbrl.ChildNodes(1)

To

Tiger = objXMLNodexbrl.ChildNodes(1).Text

ALTERNATIVLY you could

change Tiger from a string to a MSXML2.IXMLDOMNode

then change 

MsgBox Tiger

To 

MsgBox Tiger.Text

objXMLNodexbrl.ChildNodes.Length will return the count of nodes inside of XBRL

If you are trying to get a list of all Child Node Names of XBRL then Dim another MSXML2.IXMLDOMNode called nodeCurrent and use the following: 

For Each nodeCurrent In objXMLNodexbrl.ChildNodes
    Tiger = Tiger & ", " & nodeCurrent.nodeName
Next nodeCurrent

MsgBox Tiger

Upvotes: 5

Related Questions