Updating XML file using VBA

Question

I'm new in coding so help is highly appriciated. I'm trying to update an XML code using a table in excel as input. I managed to update a single attribute in the file but I want to make a loop to update multiple variables. I used the tutorial on https://excel-macro.tutorialhorizon.com/vba-excel-update-xml-file/

This code works for one attribute

Function fnUpdateXMLByTags2()
Dim mainWorkBook As Workbook
Set mainWorkBook = ActiveWorkbook
Set oXMLFile = CreateObject("Microsoft.XMLDOM")
XMLFileName = Application.GetOpenFilename(Title:="Please choose XML-file with Load Case definitions", FileFilter:="XML Files *.xml (*.xml),")
oXMLFile.Load (XMLFileName)

Set oAttribute = oXMLFile.SelectSingleNode("/project/container/table/obj[2]/p7/@v")
oAttribute.Text = mainWorkBook.Sheets("Sheet1").Range("G" & i + 2).Value

oXMLFile.Save (XMLFileName)

End Function

Something like this could be it, but I can't get it to work;

Set Attribute_A = oXMLFile.SelectNodes("/project/container/table/obj")
For i = 0 To Attribute_A.Length - 1
Set oAttribute = oXMLFile.SelectSingleNode("/project/container/table/obj[i]/p7/@v")
oAttribute.Text = mainWorkBook.Sheets("Sheet1").Range("G" & i + 2).Value
Next

Please include a sample of XML and Excel table of values and desired output for a minimal reproducible example. — Parfait
– Parfait, Commented Jan 2, 2020 at 15:26
The Microsoft namespace for XML was superseded by the MSXML2 namespace more than a decade ago. Use Msxml2.DOMDocument.6.0 instead of Microsoft.XMLDOM. This has the added advantage of making XPath the default selection language (instead of XSLPattern which is the default with Microsoft.XMLDOM) - see this question for some of the differences between the two languages — barrowc
– barrowc, Commented Jan 2, 2020 at 19:28

Stefan Drissen · Accepted Answer · 2020-01-02 15:28:18Z

2

oXMLFile.SelectSingleNode("/project/container/table/obj[i]/p7/@v")

Will literally try to get obj[i], but you do not want obj[i] you want obj[1], obj[2] etc based on i.

This should work:

oXMLFile.SelectSingleNode("/project/container/table/obj[" & i & "]/p7/@v")

answered Jan 2, 2020 at 15:28

Stefan Drissen

3,4031 gold badge15 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Updating XML file using VBA

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related