VBA retrieves XML data in Excel

Question

Difficult question about VBA Please see the pictures for your exact presentation of the problem.

If you type “SEC EDGAR” on Google, and you enter BDX in the company’s search field, click “Search,” and then in the search results, click the first “Document” button and select 10- You get into the feed. 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 its name.

, , VBA Excel ? VBA, , , , VBE .

, , - , XML- (** : XML- -.) ** <Current> 5 </Current)> , 5 , , , ****

. Microsoft XML, V6.0 VBE Tools →

Answer 1

- Microsoft XML Core Services (MSXML), . , ChildNodes objXMLNodexbrl, .

<Current> us-gaap:DebtInstrumentInterestRateStatedPercentage (, )

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