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>


ConnectionString

tblFilter

For x = 0 To ftfield.Attributes.Length - 1

2 Answers
2

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


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:UsersUserDesktopTest.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:UsersUserDesktopTest.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 contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.