VBA将XML数据拉到Excel [英] VBA pull XML data to Excel

问题描述

VBA的一个棘手问题
请参阅图片，以准确了解问题

如果您在Google上输入SEC EDGAR，您在公司搜索字段单击搜索，然后在搜索结果中单击第一个文档按钮，并选择10-K即可获得文件细节。从那里，如果我们选择XBRL INSTANCE DOCUMENT 。最后在XML文档中的元素如上图所示有元素，它的名字。

有没有办法通过使用VBA拉这个东西，把它放在我们的
首选项的Excel单元格？
我是VBA的初学者，所以请善待一些更多的代码解释，还有从VBE中的工具添加的参考资料以及为什么。$ /

> 我实际上正在寻找的是一个代码块，用于抓取XML元素之间的数据（**重要提示：XML实例文档应在提供的Web位置中。）**例如在< Current> 5 < / Current）> 将拉出5并填充指定在代码块和一些说明，所以我可以坚持到它并研究代码****

这个问题是答案。在答案中编写代码的订单，您必须在VBE的工具上引用Microsoft XML，V6.0 - >参考资料

解决方案

您还应该尝试并了解 Microsoft XML核心服务（MSXML），但是让您开始，您可以从以下开始，并根据需要进行修改。如果您需要很多值，那么值得循环使用 objXMLNodexbrl 的 ChildNodes ，因为很多节点也似乎被复制。

而不是在我的示例中使用< Current> 我使用了 us-gaap：DebtInstrumentInterestRateStatePercentage （您在问题中圈出的那个）

  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 
 
设置objXMLHTTP =新的MSXML2.XMLHTTP 
设置objXMLDoc =新的MSXML2.DOMDocument 
 
 strXMLSite =http://www.sec.gov/Archives/edgar/data /10795/000119312513456802/bdx-20130930.xml
 
 objXMLHTTP.OpenPOST，strXMLSite，False 
 objXMLHTTP.send 
 objXMLDoc.LoadXML（objXMLHTTP.responseText）
 
设置objXMLNodexbrl = objXMLDoc.SelectSingleNode（xbrl）
 
设置objXMLNodeDIIRSP = objXMLNodexbrl.SelectSingleNode（us-gaap：DebtInstrumentInterestRateStatedPercentage）
 
工作表（ 工作表Sheet1）。穰e（A1）。Value = objXMLNodeDIIRSP.Text 
 End Sub 
  

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

解决方案

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

这篇关于VBA将XML数据拉到Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！