Update Value for XML with VBA

Question

I have an XML with the following structure

I need to change the value for messageId with something else (does not really matter what, just something else).

I tried several codes found online, such as this one here:

Sub test()

    Dim myVar As String, pathToXML As String
    Dim xmlDoc As Object, xmlRoot As Object
    Set xmlDoc = CreateObject("MSXML2.DOMDocument")
    pathToXML = "C:\Users\path\test.xml" '<--- change the path
    Call xmlDoc.Load(pathToXML)
    Set xmlRoot = xmlDoc.getElementsByTagName("header").Item(2)
    myVar = "Test" '<--- your value
    xmlRoot.SelectSingleNode("n6:messageId").Text = myVar
    Call xmlDoc.Save(pathToXML)
    
End Sub

But had no success. I noticed that whenever I use .Text that I run into a runtime error (91).

Anyone got an idea?

Maybe this one helps you: https://stackoverflow.com/q/63074067/16578424. It looks like `getElemntsByTagName` doesn't work with namespaces. Try using `selectNodes` instead — Ike, Nov 15 '22 at 14:06
You need to add at least the namespace with alias "n6" - see for example https://stackoverflow.com/a/64902141/478884 — Tim Williams, Nov 15 '22 at 16:51

Oran G. Utan · Answer 1 · 2022-11-15T23:32:54.617

You may have the transformation done by an XSLT file (saved as ChangeTag.xsl). I am not sure if you wanted to change the tag name or the value inside.

XSLT (to change the tag name)

(adapted from https://stackoverflow.com/a/7246969/18247317)

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:template match="@*|node()">
        <xsl:copy>
            <xsl:apply-templates select="@*|node()" />
        </xsl:copy>
    </xsl:template>
    <xsl:template match="n6:messageId">
        <NewName><xsl:apply-templates select="@*|node()" /></NewName>
    </xsl:template>
</xsl:stylesheet>

XSLT (to change the value inside the tag)

(adapted from https://stackoverflow.com/a/10430719/18247317)

<xsl:stylesheet version="1.0"
 xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
 <xsl:output omit-xml-declaration="yes" indent="yes"/>
 <xsl:strip-space elements="*"/>

 <xsl:param name="pReplacement" select="'NewValue'"/>

 <xsl:template match="node()|@*">
     <xsl:copy>
       <xsl:apply-templates select="node()|@*"/>
     </xsl:copy>
 </xsl:template>

 <xsl:template match="header/n6:messageId/text()">
  <xsl:value-of select="$pReplacement"/>
 </xsl:template>
</xsl:stylesheet>

called by VBA in this way:

VBA

Sub ChangeTag()

Dim StrFileName As String
Dim StrFolder As String
Dim StrFolderTarget As String

Dim xmldoc As Object
Dim xsldoc As Object
Dim newdoc As Object

With Application.FileDialog(msoFileDialogFolderPicker)
    .Title = "Select the folder where XML file is stored"
        If .Show = -1 Then
            StrFolder = .SelectedItems(1) & "\"
        End If
End With

With Application.FileDialog(msoFileDialogFolderPicker)
    .Title = "Select the folder where the edited XML file should be stored"
        If .Show = -1 Then
            StrFolderTarget = .SelectedItems(1) & "\"
        End If
End With

Set xmldoc = CreateObject("MSXML2.DOMDocument")
Set xsldoc = CreateObject("MSXML2.DOMDocument")
Set newdoc = CreateObject("MSXML2.DOMDocument")

StrFileName = Dir(StrFolder & "*.xml")

'Load XML
xmldoc.async = False
xmldoc.Load StrFileName

'Load XSL
xsldoc.async = False
xsldoc.Load StrFolder & "\" & "ChangeTag.xsl"

'Transform
xmldoc.transformNodeToObject xsldoc, newdoc
newdoc.Save StrFolderTarget & "Edited" & StrFileName


End Sub

Some editing maybe necessary in the VBA as I have quickly edited the one I use for other transformations on SVG files.

EDIT: the .xsl file should be in the same folder of the source file with this macro unless you change this line xsldoc.Load StrFolder & "\" & "ChangeTag.xsl"

Update Value for XML with VBA

1 Answers1