1

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
Job81
  • 13
  • 3
  • 1
    Please include a sample of XML and Excel table of values and desired output for a [mcve]. – Parfait Jan 02 '20 at 15:26
  • 1
    The `Microsoft` namespace for XML was superseded by the `MSXML2` namespace [more than a decade ago](https://blogs.msdn.microsoft.com/xmlteam/2006/10/23/using-the-right-version-of-msxml-in-internet-explorer/). 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](https://stackoverflow.com/q/2099880/2127508) for some of the differences between the two languages – barrowc Jan 02 '20 at 19:28

1 Answers1

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")

Stefan Drissen
  • 3,266
  • 1
  • 13
  • 21