Edit XML through Excel VBA

Question

I need to update a "simple" XML file with new values and save with a new name. For a test I am only trying to update one value. But using the code below I get an error:

Run-time error '91': Object variable or With block variable not set

VBA:

Sub XMLTest()
Dim myVar As String, pathToXML As String
Dim xmlDoc As Object, xmlRoot As Object
    Set xmlDoc = CreateObject("MSXML2.DOMDocument")
    pathToXML = "C:\Users\Path_to_XML\PJMeasurements.xml" '<--- Update path

    Call xmlDoc.Load(pathToXML)
    Set xmlRoot = xmlDoc.getElementsByTagName("ns0:MeasurementsSO").Item(0) '<--- Is this correct?
    myVar = "9999-9999999" '<--- Update value
    xmlRoot.SelectSingleNode("SalesOrderNo").Text = myVar

    Call xmlDoc.Save(pathToXML)
End Sub

This is the XML:

<ns0:MeasurementsSO xmlns:ns0="http://update.DocumentTypes.Schema.PJ Measurement.Xml">
  <SalesOrderNo>23482-4612310</SalesOrderNo>
  <Weight>83</Weight>
  <Volume>0,03</Volume>
  <Numberofcolli>1</Numberofcolli>
</ns0:MeasurementsSO>

I guess, there's extra space in the namespace PJ Measurement.Xml? — JohnyL
– JohnyL, Commented May 27, 2020 at 7:53

JohnyL · Accepted Answer · 2020-05-27 07:51:10Z

1

You need to add namespace:

Sub XMLTest()
    Dim myVar As String, pathToXML As String
    Dim xmlDoc As Object, xmlNode As Object
    Set xmlDoc = CreateObject("MSXML2.DOMDocument")
    pathToXML = "C:\Temp\PJMeasurements.xml" '<--- Update path
    xmlDoc.setProperty "SelectionNamespaces", "xmlns:ns0='http://update.DocumentTypes.Schema.PJMeasurement.Xml'"
    Call xmlDoc.Load(pathToXML)
    Set xmlNode = xmlDoc.SelectSingleNode("/ns0:MeasurementsSO/SalesOrderNo")
    myVar = "9999-9999999" '<--- Update value
    xmlNode.Text = myVar
    Call xmlDoc.Save(pathToXML)
End Sub

answered May 27, 2020 at 7:51

JohnyL

7,1845 gold badges27 silver badges48 bronze badges

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

2 Comments

Gjeep Over a year ago

Oh, i didnt see your reply. I found an solution myself by updating GetElementsByTagName from: MeasurementsSO to ns0:MeasurementsSO. Is your solution more correct?

JohnyL Over a year ago

@Gjeep 1) You didn't add namespace. 2) It selects the element you need in one step 😉

Gjeep · Accepted Answer · 2020-05-27 07:54:41Z

0

Found the error my self.

GetElementsByTagName was wrong.

Updated above.

answered May 27, 2020 at 7:54

Gjeep

275 bronze badges

Collectives™ on Stack Overflow

Edit XML through Excel VBA

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related