VBA Excel: Loop through XML Attributes

Question

I'm actually building a programme in excel to fill in data into a word document from a webservice. Because the XML is built up in a strange way, I have to loop through the attributes of a node, to create a list. From this list I will create a table.

My problem is this loop, I'll show you the code:

    ConnectionString = "//GetConfigurationItems/ConfigurationItem/AttachmentTypes/AttachmentType"
tblFilter = "1D8651D1-99E2-4D77-9BFF-1A667AA9398D"
id = "14"

            Set valuesOXML = CreateObject("msxml2.DOMDocument.4.0")
            valuesOXML.LoadXML DMIService.execute(Webservice, functionName, portName, "<![CDATA[<GetConfigurationItems><ConfigurationItem ID=""" & ID & """ Filter=""" & tblFilter & """ Deleted=""0""/></GetConfigurationItems>]]>")

            Set fourthNameField = valuesOXML.SelectNodes(ConnectionString)
            For Each ftfield In fourthNameField
                werte = werte & ftfield.Attributes(0).Text & ";"

                Dim x As Integer
                For x = 0 To ftfield.Attributes.Item - 1
                     Debug.Print ftfield.Attributes.Item(x)
                Next x
            Next ftfield

The command to save the werte variable is working. but the Loop through the attributes fails with the failure-Text:

"Object doens't support this property or method".

The XML looks like this:

<?xml version="1.0"?>
<GetConfigurationItems Error="False">
    <ConfigurationItem ID="14" Filter="1D8651D1-99E2-4D77-9BFF-1A667AA9398D" Deleted="0">
        <AttachmentTypes DropDownType="14" Filter="1D8651D1-99E2-4D77-9BFF-1A667AA9398D" Deleted="0">
            <AttachmentType ShortDesc="BOA_FIT" VersionNo="2" ID="1D8651D1-99E2-4D77-9BFF-1A667AA9398D">FIT</AttachmentType>
        </AttachmentTypes>
    </ConfigurationItem>
</GetConfigurationItems>

what is `ConnectionString`? I can't see it in the XML provided. Also, what value is given to `tblFilter`? used in the *LoadXML* — Our Man in Bananas, Sep 07 '18 at 11:46
sorry i forgot to mention this one.. its initialized further up.. ConnectionString = //GetConfigurationItems/ConfigurationItem/AttachmentTypes/AttachmentType — LucaS, Sep 07 '18 at 11:47
AFAIK MSXML2 Version 4.0 is obsolete, try Version 6.0 instead ; cf https://stackoverflow.com/questions/52221640/vba-excel-loop-trough-xml-attributes#52221640 — T.M., Sep 07 '18 at 12:00
PS - correct link is [Obtain attribute names from XML using VBA](https://stackoverflow.com/questions/51887820/obtain-attribute-names-from-xml-using-vba/51919182#51919182) — T.M., Sep 07 '18 at 12:34
thanks for the link. also very helpfull. and also thanks axel richter, your answer is also the right solution for what i was looking for.. sometimes the solution is so easy :) but i was just stuck somehow.. — LucaS, Sep 07 '18 at 12:38

QHarr · Answer 1 · 2018-09-07T12:52:48.100

Are you not doing the equivalent of the following? Replace my loading from file with your connection code.

Option Explicit
Public Sub testing()
    Dim xmlDoc As New MSXML2.DOMDocument60, mainNode As Object, Node As Object, child As Object, child2 As Object
    Set xmlDoc = New MSXML2.DOMDocument60
    xmlDoc.Load "C:\Users\User\Desktop\Test.xml"
    Set mainNode = xmlDoc.SelectNodes("//GetConfigurationItems")

    For Each Node In mainNode
        For Each child In Node.ChildNodes
            Debug.Print child.BaseName & " :" & child.Text
            For Each child2 In child.ChildNodes
               Debug.Print child2.BaseName & " : " & child2.Text
            Next
        Next
    Next Node
End Sub

With attributes:

Option Explicit
Public Sub testing()
    Dim xmlDoc As New MSXML2.DOMDocument60, mainNode As Object, Node As Object, child As Object, child2 As Object, i As Long
    Set xmlDoc = New MSXML2.DOMDocument60
    xmlDoc.Load "C:\Users\User\Desktop\Test.xml"
    Set mainNode = xmlDoc.SelectNodes("//GetConfigurationItems")

    For Each Node In mainNode
        For Each child In Node.ChildNodes
            If child.Attributes.Length > 0 Then
                For i = 0 To child.Attributes.Length - 1
                    Debug.Print child.BaseName & " : " & child.Attributes(i).nodeName & " - " & child.Attributes(i).NodeValue; ""
                Next
            End If
            For Each child2 In child.ChildNodes
                If child2.Attributes.Length > 0 Then
                For i = 0 To child2.Attributes.Length - 1
                    Debug.Print child2.BaseName & " : " & child2.Attributes(i).nodeName & " - " & child2.Attributes(i).NodeValue
                Next
            End If
            Next
        Next
    Next Node
End Sub

Thanks for your reply, but its not what a try to achieve.. i have to get a list with all the attributes from a node.. but thanks for your help anyway!! the other guys gave me the solution :) — LucaS, Sep 07 '18 at 12:33
oops... my bad... so you wanted a list of the attrbibutes and their values? Or just the attributes? — QHarr, Sep 07 '18 at 12:34
first just the attribute names.. because im gonna create a table out of the list of attributes.. later i will also need to get the values. but thats not the problem of today :) — LucaS, Sep 07 '18 at 12:37

score 0 · Accepted Answer · answered Sep 07 '18 at 12:05

try this (works in VBA using the XML as as string)

I added MSXML2 library as a Reference (Tools > References > Microsoft XML, v6.0)**

Dim valuesOXML As MSXML2.DOMDocument60 

Dim ConnectionString As String
Dim fourthNameField As IXMLDOMNodeList
Dim ftfield As IXMLDOMNode
Dim werte As String

ConnectionString = "//GetConfigurationItems/ConfigurationItem/AttachmentTypes/AttachmentType"

Set valuesOXML = CreateObject("Msxml2.DOMDocument.6.0")

valuesOXML.LoadXML  DMIService.execute(Webservice, functionName, portName, "<![CDATA[<GetConfigurationItems><ConfigurationItem ID=""" & ID & """ Filter=""" & tblFilter & """ Deleted=""0""/></GetConfigurationItems>]]>")

Set fourthNameField = valuesOXML.SelectNodes(ConnectionString)
For Each ftfield In fourthNameField
    werte = werte & ftfield.Attributes(0).Text & ";"

    Dim x As Integer
    For x = 0 To ftfield.Attributes.Length - 1
         Debug.Print ftfield.Attributes.Item(x).Text
    Next x
Next ftfield
End Sub

Hi, this is exactly the case.. with For x = 0 To ftfield.Attributes.Length - 1 it is working. thanks a lot. i just started with stackoverflow and this was my first question. im overwhelmed by all the people try to help me so fast! thanks a lot!! — LucaS, Sep 07 '18 at 12:35
@LucaS: Your're welcome, so long as you ask a good question like that, with example code, clearly explaining the problem, you're likely to get good useful, helpful responses and upvotes, which gives you reputaion — Our Man in Bananas, Sep 07 '18 at 12:53

VBA Excel: Loop through XML Attributes

2 Answers2