vba 如何在excel中使用vba访问xml中的特定元素和属性？

How to access a specific element and attribute in xml with vba in excel?

提问by user2513952

I'm struggling with getting excel to parse through an xml file. I've found a plethora of examples, but none seem to be quite what I am looking for and I can't seem to get past the error "Object variable or With block variable not set"

我正在努力让 excel 解析一个 xml 文件。我找到了大量的例子，但似乎没有一个是我正在寻找的，我似乎无法克服错误“对象变量或块变量未设置”

The xml is well formed and looks like the following:

xml 格式良好，如下所示：

<xml tag>
<PLMXML>
  <WorkflowTemplate name="">
    <argument name="">
    </argument>
  </WorkflowTemplate >
  <WorkflowTemplate name="">

etc.

等等。

I'm trying to use VBA to get to the value of all the Children's names individually and get to the names of the arguments. I've keep getting the error with this code:

我正在尝试使用 VBA 分别获取所有儿童名称的值并获取参数的名称。我一直收到此代码的错误：

Dim xmlDoc As MSXML2.DOMDocument
Dim xmlElement As MSXML2.IXMLDOMElement
Dim xmlNode As MSXML2.IXMLDOMNode
Dim xmlAttribute As MSXML2.IXMLDOMAttribute

Set xmlDoc = New MSXML2.DOMDocument
xmlDoc.async = False
xmlDoc.validateOnParse = False

'ENTER THE PATH WHERE THE XML Workflow DOCUMENT IS STORED:
Dim DocumentPath As String
DocumentPath = InputBox("Enter the full path for the xml workflow document, example: C:\workflows\workflowseasy.xml", "Workflow XML File path", "C:\workflows\workflowseasy.xml")
xmlDoc.Load (DocumentPath)

Set xmlElement = xmlDoc.DocumentElement

Set xmlNode = xmlElement.SelectSingleNode("WorkflowTemplate[0]")


Set xmlAtribute = xmlNode.Attributes.getNamedItem("name")

I'm not clear on how to get to the data in the document using this parser in excel vba. Any help would be greatly appreciated. I currently have Microsoft XML, v6.0 selected in references.

我不清楚如何在 excel vba 中使用此解析器获取文档中的数据。任何帮助将不胜感激。我目前在参考资料中选择了 Microsoft XML, v6.0。

UPDATE

更新

I've been digging more into it and have come up with the following code, though I still get the same error:

我一直在深入研究它并提出了以下代码，尽管我仍然遇到相同的错误：

Dim xmlDoc As MSXML2.DOMDocument60
Dim xmlRoot As MSXML2.IXMLDOMNode
Dim xmlTemplate As MSXML2.IXMLDOMNode
Dim xmlAttributes As MSXML2.IXMLDOMNamedNodeMap
Dim xmlName As MSXML2.IXMLDOMNode
Dim xmlChildren As MSXML2.IXMLDOMNodeList
Dim xmlChild As MSXML2.IXMLDOMNode
Dim intI As Long
intI = 1

Set xmlDoc = New MSXML2.DOMDocument60
xmlDoc.async = False
xmlDoc.validateOnParse = False

'ENTER THE PATH WHERE THE XML Workflow DOCUMENT IS STORED:
Dim DocumentPath As String
DocumentPath = InputBox("Enter the full path for the xml workflow document, example: C:\workflows\workflowseasy.xml", "Workflow XML File path", "C:\workflows\workflowseasy.xml")
xmlDoc.Load (DocumentPath)

Set xmlRoot = xmlDoc.DocumentElement *****these say they are empty when debugging
Set xmlChildren = xmlRoot.ChildNodes *****these say they are empty when debugging
For Each xmlTemplate In xmlChildren  *****error occures here
    If xmlTemplate.nodeName = "WorkflowTemplate" Then
    Set xmlAttributes = xmlTemplate.Attributes
    Set xmlName = xmlAttributes.getNamedItem("name")
    ActiveSheet.Cells(int1, 1).Value = xmlName.Text
    Set xmlChildren = xmlTemplate.ChildNodes
    intI = intI + 1
    End If
Next xmlTemplate

FINAL UPDATE**

最终更新* *

Figured it out. The loading of the file was the issue. For some reason passing it the string from a msg box doesn't work, but passing it from the gui file selector does. Here's the code I ended up using.

弄清楚了。文件的加载是问题。出于某种原因，从 msg 框中传递字符串不起作用，但从 gui 文件选择器传递它可以。这是我最终使用的代码。

    Dim xmlDoc As MSXML2.DOMDocument60
Dim xmlRoot As MSXML2.IXMLDOMNode
Dim xmlTemplate As MSXML2.IXMLDOMNode
Dim xmlAttributes As MSXML2.IXMLDOMNamedNodeMap
Dim xmlName As MSXML2.IXMLDOMNode
Dim xmlChildren As MSXML2.IXMLDOMNodeList
Dim xmlChild As MSXML2.IXMLDOMNode
Dim intI As Long
intI = 1

Set xmlDoc = New MSXML2.DOMDocument60
xmlDoc.async = False
xmlDoc.validateOnParse = False

'ENTER THE PATH WHERE THE XML Workflow DOCUMENT IS STORED:
Dim DocumentPath As String
With Application.FileDialog(msoFileDialogOpen)
.Title = "Choose File"
.AllowMultiSelect = False
.Show
'DocumentPath.Show
DocumentPath = .SelectedItems(1)
End With

xmlDoc.Load (DocumentPath)

Set xmlRoot = xmlDoc.DocumentElement
Set xmlChildren = xmlRoot.ChildNodes
For Each xmlTemplate In xmlChildren
    If xmlTemplate.nodeName = "WorkflowTemplate" Then
    Set xmlAttributes = xmlTemplate.Attributes
    Set xmlName = xmlAttributes.getNamedItem("name")
    ActiveSheet.Cells(int1, 1).Value = xmlName.Text
    Set xmlChildren = xmlTemplate.ChildNodes
    intI = intI + 1
    End If
Next xmlTemplate

Currently the code breaks on the assigning value section, but going through the code the variables are pulling in the correct values and pulling in the xml information correctly.

目前代码在赋值部分中断，但通过代码变量正在拉入正确的值并正确拉入 xml 信息。

采纳答案by ChrisProsser

I think that the final code presented in the question may not always traverse the whole xml document as the xmlChildren variable is overridden during the loop so I think this may just get the first child node and it's first child and so on.

我认为问题中提供的最终代码可能并不总是遍历整个 xml 文档，因为 xmlChildren 变量在循环期间被覆盖，所以我认为这可能只是获取第一个子节点，它是第一个子节点等等。

To traverse the whole document you can call a separate procedure and build in a recursive call so that it will follow each of the child nodes but then return back to the list it was called from when done.

要遍历整个文档，您可以调用一个单独的过程并构建一个递归调用，以便它遵循每个子节点，但在完成后返回到调用它的列表。

Here is a simplified example where I am looking for all instances of a specific xml element, say all carts in an xml doc containing:

这是一个简化的示例，我在其中查找特定 xml 元素的所有实例，例如 xml 文档中的所有购物车，其中包含：

<?xml version="1.0" encoding="UTF-8"?>
<ImportConfig>
    <ShoppingCarts description="Any carts added here will be picked up by the auto import">
        <cart>shopping cart 1 name here</cart>
        <cart>shopping cart 2 name here</cart>
    </ShoppingCarts>
</ImportConfig>

The first procedure below is specific for this example i.e. where the tags are named etc, but the other two can be used generically for any xml document (the first is just an example of how they can be used):

下面的第一个过程是特定于这个例子的，即标签的名称等，但其他两个可以一般用于任何 xml 文档（第一个只是如何使用它们的示例）：

' Chris Prosser 09/07/2014
' example use of getElementList (in this case to get all cart elements)

Sub getCarts()
    Dim carts As Collection
    Dim i As Integer

    Set carts = New Collection
    getElementList "C:\Users\Chris\Dropbox\VBAutomation\AutoImportConfig.xml", "cart", carts

    For i = 1 To carts.count
        Debug.Print carts.Item(i)
    Next

End Sub

' Chris Prosser 09/07/2014
' Gets the values of all instances of a specific element from an xml file

Sub getElementList(xml_file_path As String, _
                          elementName As String, _
                          elementValuesList As Collection)

    Dim xmlDoc As MSXML2.DOMDocument
    Dim xmlRoot As MSXML2.IXMLDOMNode
    Dim xmlChildren As MSXML2.IXMLDOMNodeList
    Dim xmlElement As MSXML2.IXMLDOMElement

    Set xmlDoc = New MSXML2.DOMDocument
    xmlDoc.async = False
    xmlDoc.validateOnParse = False
    xmlDoc.Load (xml_file_path)
    Set xmlRoot = xmlDoc.documentElement
    Set xmlChildren = xmlRoot.childNodes

    iterateOverChildNodes xmlChildren, elementName, elementValuesList

End Sub

' Chris Prosser 09/07/2014
' Call with a list of xmlNodes (can be generated from a file using getElementList)
' and an element name to search for. The procedure find child nodes and re-runs
' recursively until all branchs from the list of nodes passed in have been traversed

Sub iterateOverChildNodes(xmlChildren As MSXML2.IXMLDOMNodeList, _
                          elementName As String, _
                          elementValuesList As Collection)

    Dim xmlElement As MSXML2.IXMLDOMElement
    Dim xmlGrandChildren As MSXML2.IXMLDOMNodeList

    For Each xmlElement In xmlChildren
        If xmlElement.nodeName = elementName Then
            'Debug.Print xmlElement.nodeTypedValue
            elementValuesList.Add xmlElement.nodeTypedValue
        Else
            Set xmlGrandChildren = xmlElement.childNodes
            iterateOverChildNodes xmlGrandChildren, elementName, elementValuesList
        End If
    Next xmlElement

End Sub