VBA pull XML data to Excel

by

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

More Related Contents:

  1. Convert image (jpg) to base64 in Excel VBA?
  2. How to parse XML using vba
  3. html parsing of cricinfo scorecards
  4. Obtain attribute names from XML using VBA
  5. XML parse VBA excel (function trip, & MSXML2.DOMDocument)
  6. Looking for a clear description of Excel’s .xlsx XML format [closed]
  7. VBA Output to file using UTF-16
  8. What’s the best way to export UTF8 data into Excel?
  9. How do you change the value of a cell in excel by clicking on it using VBA? [closed]
  10. How do I get values from a hidden worksheet using VBA [closed]
  11. VBA Macro for copying and pasting columns on same excel sheet
  12. Levenshtein Distance in VBA [closed]
  13. Run Excel Macro from Outside Excel Using VBScript From Command Line
  14. Can Chrome be made to perform an XSL transform on a local file?
  15. VBA Runtime Error 1004 “Application-defined or Object-defined error” when Selecting Range
  16. POST XML file using cURL command line
  17. Assign event handlers to controls on user form created dynamically in VBA
  18. Filling any empty cells with the value above
  19. Delete cells in an Excel column when rows = 0
  20. What does ReDim Preserve do?
  21. How to copy data from closed workbooks (keeping them closed) into master workbook using VBA?
  22. How do I reference tables in Excel using VBA?
  23. Excel Select vs Activate
  24. What browsers support Xpath 2.0?
  25. Fast compare method of 2 columns [closed]
  26. Fastest way to check if two ranges are equal in excel vba [duplicate]
  27. Scala – modifying nested elements in xml
  28. VBA inheritance, analog of super
  29. How to protect Excel workbook using VBA?
  30. XSLT Transform XML with Namespaces

Leave a Comment