Reputation: 27
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>
Upvotes: 0
Views: 826
Reputation: 27
Found the error my self.
GetElementsByTagName was wrong.
Updated above.
Upvotes: 0
Reputation: 7162
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
Upvotes: 1