How to access a specific element and attribute in

Question

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"

The xml is well formed and looks like the following:

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

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.

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.

    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.

Answer 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.

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

' 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