使用VBA读取XML:列出所有出现的标记，包括contextRef

Question

我是VBA和XML的新手，非常感谢您的帮助。我有以下代码:

Option Explicit
Sub XMLfromPPTExample()
Dim XDoc As MSXML2.DOMDocument
Dim objxmlnodexbrl As MSXML2.IXMLDOMNode
Dim objXMLNodeDIIRSP As MSXML2.IXMLDOMNode
Set XDoc = New MSXML2.DOMDocument
XDoc.async = False
XDoc.validateOnParse = False
XDoc.Load("https://www.sec.gov/Archives/edgar/data/936468/000119312516476010/lmt-20151231.xml")
Set objxmlnodexbrl = XDoc.SelectSingleNode("xbrl")
Set objXMLNodeDIIRSP = objxmlnodexbrl.SelectSingleNode("us-gaap:GrossProfit")
Worksheets("Tabelle1").Range("A1").Value = objXMLNodeDIIRSP.Text
End Sub

这将访问SEC的Edgar数据库上的特定xml表，并将定义的标签("us-gaap:GrossProfit")的值写入Excel字段A1。

但是，这个标记可能在这个xml文件中以不同的值出现多次。我需要的是，这些出现的每一个都被打印到一个excel表中，包括"contextRef"的值和值。

你能修改我的代码使它工作吗?非常感谢。

Answer 1

考虑使用MSXML DOM的XPath，确保声明名称空间us:gaap。下面按每个<us-gaap:grossProfit>标签的节点索引[#]迭代:

Sub XMLfromPPTExample()
On Error GoTo ErrHandle
    Dim XDoc As MSXML2.DOMDocument
    Dim objxmlnodexbrl As MSXML2.IXMLDOMNode
    Dim grossProfitList As MSXML2.IXMLDOMNodeList
    Dim XmlNamespaces As String
    Dim row As Integer
    Set XDoc = New MSXML2.DOMDocument
    XDoc.async = False
    XDoc.validateOnParse = False
    XDoc.Load ("https://www.sec.gov/Archives/edgar/data/936468/000119312516476010/lmt-20151231.xml")
    XmlNamespaces = "xmlns:us-gaap='http://fasb.org/us-gaap/2015-01-31'"
    XDoc.setProperty "SelectionNamespaces", XmlNamespaces
    XDoc.setProperty "SelectionLanguage", "XPath"
    Set grossProfitList = XDoc.DocumentElement.SelectNodes("//us-gaap:GrossProfit")
    For row = 1 To grossProfitList.Length
        Worksheets("Tabelle1").Range("A" & row).Value = XDoc.DocumentElement.SelectNodes("//us-gaap:GrossProfit[" & row & "]")(0).Text
        Worksheets("Tabelle1").Range("B" & row).Value = XDoc.DocumentElement.SelectNodes("//us-gaap:GrossProfit[" & row & "]/@contextRef")(0).Text
    Next row
    Set grossProfitList = Nothing
    Set XDoc = Nothing
    Exit Sub
ErrHandle:
    MsgBox Err.Number & " - " & Err.Description
    Exit Sub
End Sub

4187000000  eol_PE11373---1510-K0017_STD_365_20131231_0
5255000000  eol_PE11373---1510-K0017_STD_365_20141231_0
5200000000  eol_PE11373---1510-K0017_STD_365_20151231_0