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

Question

I'm struggling to parse an XML file. I found many examples, but none seem to be exactly what I am looking for, and I cannot get through the error "Object variable or variable block not set"

xml is well formed and looks like this:

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

etc..

I am trying to use VBA to get the value of all the names of children individually and get the names of the arguments. I 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 do not understand 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 links.

UPDATE

I already burst into it and came up with the following code, although 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. Problem downloading file. For some reason, passing this line from the msg field does not work, but passing it from the file selector gui does. Here is the code I used.

  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 is broken down into a value assignment section, but passing through the code, the variables pull the correct values ​​and correctly extract the xml information.

Answer 1

I think the final code presented in the question may not always traverse the entire XML document, since the xmlChildren variable is redefined during the loop, so I think it can just get the first child node, and this is the first child and so on.

To go through the entire document, you can call a separate procedure and build a recursive call so that it matches each of the child nodes, but then returns back to the list from which it was called from the moment it was completed.

Here is a simplified example where I look for all instances of a specific xml element, say, all carts in an xml document 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 to this example, i.e. where tags are called, etc., but the other two can be used in general terms for any XML document (the first example 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