Can I update an XML node value using a variable in VBA

Question

I am a complete newcomer to learning about XML but OK with VBA in Excel 2010.

In Excel VBA I have created a simple CustomXMLPart with 5 nodes under a single root, akin to the example below:

<
  <RefTest>
      <sRef1>SomeText</sRef1>    'text
      <sRef2>XYZ234</sRef2>      'text
      <sRef3>ABC123</sRef3>      'text
      <dRef4>25/02/1953</dRef4>  'date or text?
      <iRef5>0</iRef5>           'numeric or text?
  </RefTest>
>

This works OK and I can read the values back-in using VBA.

My problem is that the node values (at the moment) are entered as literals (text and digits).

I want to be able to update these node values, from within Excel VBA, but using the contents of VBA variables.

So, for example a user enters a value into a userform text box, into a variable (say MyVar), and I want to update the node value with the contents of this variable. A sort of "update node iRef5 with MyVar". I can find very little reference to Updating XML values like this, especially using variables, on the web.

Can this be done from within VBA? If so, what is the approach, how does XML deal with variables, and perhaps an example of the exact syntax please.

With all thanks in anticipation.

Answer 1

I don't know what structure of your xml will have. But if it's only these five nodes, perhaps something like this might help you:

Sub XMLTest()
Dim myVar As String, pathToXML As String
Dim xmlDoc As Object, xmlRoot As Object
    Set xmlDoc = CreateObject("MSXML2.DOMDocument")
    pathToXML = "N:\example.xml" '<--- change the path
    Call xmlDoc.Load(pathToXML)
    Set xmlRoot = xmlDoc.getElementsByTagName("RefTest").Item(0)
    myVar = "foobar" '<--- your value
    xmlRoot.selectSingleNode("iRef5").Text = myVar
    Call xmlDoc.Save(pathToXML)
End Sub

If this doesn't help you, you should give more information about your xml and what you actually want to do.