VBA pull XML data to Excel

Question

A tricky question on VBA Please see pictures for your accurate view of the problem

If you type on Google "SEC EDGAR" , and you enter BDX in the Company Search field click Search and then in the search results you click the first Document button and you choose 10-K you get to the filing Detail. From there if we choose XBRL INSTANCE DOCUMENT . Finally inside the XML document in the elements as you can see in the last picture we have the element and it's name.

Is there anyway to pull this thing by using VBA and put it in the Excel cell of our preference? I am a beginner in VBA so please be kind to also explain the code a little more and also what References are we adding from Tools in VBE and why.

What i actually am looking for is a code block which grabs the data between the XML elements (**Important: The XML Instance document should be in the Web location provided.) **For example in <Current> 5 </Current)> something that will pull the 5 and populate a cell designated in the code block and some instructions so i can hold on to it and study the code****

THIS QUESTION IS ANSWERED. IN ORDER FOR THE CODE IN THE ANSWER TO WORK YOU MUST HAVE TURNED ON THE Microsoft XML, V6.0 on the VBE's Tools-->References

Answer 1

You should still try and learn about Microsoft XML Core Services (MSXML) but to get you started you can start with the following and modify it as needed. If you need a lot of values it would be worth looping the ChildNodes of objXMLNodexbrl as a lot of the nodes also seem to be duplicated.

instead of using <Current> in my sample I used an actual value for us-gaap:DebtInstrumentInterestRateStatedPercentage (the one you had circled in your question)

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

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")

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

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