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

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单独获取所有Children名称的值，并获取参数的名称。我一直在用这段代码得到错误：

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

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信息。

1 个解决方案

#1

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 doc中包含以下内容的所有购物车：

<?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):

下面的第一个过程特定于此示例，即标签被命名为etc，但其他两个一般可用于任何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