CODEWITHSUNDEEP
excelxmlvba
LucaS
LucaS

Reputation: 287

VBA Excel: Loop through XML Attributes

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>

Upvotes: 2

Views: 1852

Answers (2)

QHarr
QHarr

Reputation: 84465

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

Upvotes: 1

Our Man in Bananas
Our Man in Bananas

Reputation: 5981

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

Upvotes: 0

Related Questions