Replace string with XSLT through VBA

Question

EDIT: I have corrected the code I had shared previously as there were some errors. I have tried the below suggestions Set xmldoc = CreateObject("MSXML2.DOMDocument.3.0") and Set xmldoc = CreateObject("MSXML2.DOMDocument.6.0")) with no success.

---

To give some context, the SVG is generated, through an Excel file I prepare, from a third party software I feed the Excel file with, so the word Item is the keyword I use to mark those paths for which I want the text to appear, this removal is to clean up the resulting SVG.

---

I would like to remove the string Item inside tspan, so from <tspan id="Item1-tspan" x="" y="">Item1</tspan> to <tspan id="Item1-tspan" x="" y="">1</tspan>.

I have tried all possible solutions, yet I am not able to replace text with XSLT through VBA. I would like to remove the word "Item" and I went through every single answer I found on StackOverflow and in othtr websites. I either do not get the wanted result or I get errors.

I call it with this simple macro:

VBA

Sub AddTextToSVGReplace()

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 the vector 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 vector 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 & "*.svg")

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

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

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


End Sub

This is the SVG file I would like to transform

SVG (extract, only relevant part)

<g id="symbols-svg">
<g id="Item1-svg" transform="translate(105, 210)">
<path d="M-5 0a5 5 0 1 0 10 0 5 5 0 1 0-10 0Z" stroke="rgb(200, 200, 200)" id="Item1" style="fill: rgb(0, 15, 60); stroke-width: 1; fill-opacity: 0.9; stroke-opacity: 0.5; stroke-linejoin: miter; stroke-linecap: butt; stroke: rgb(0, 50, 100);">
</path>
<text x="" y="" id="Item1-text" style="-inkscape-font-specification:'Calibri, Normal';font-family:Calibri;font-weight:normal;font-style:normal;font-stretch:normal;font-variant:normal;font-size:20px;font-variant-ligatures:normal;font-variant-caps:normal;font-variant-numeric:normal;font-variant-east-asian:normal;fill:#000000">
<tspan id="Item1-tspan" x="" y="">Item1</tspan>
</text>
</g>
<g id="Item2-svg" transform="translate(250, 90)">
<path d="M-5 0a5 5 0 1 0 10 0 5 5 0 1 0-10 0Z" stroke="rgb(200, 200, 200)" id="Item2" style="fill: rgb(0, 15, 60); stroke-width: 1; fill-opacity: 0.9; stroke-opacity: 0.5; stroke-linejoin: miter; stroke-linecap: butt; stroke: rgb(0, 50, 100);">
</path>
<text x="" y="" id="Item2-text" style="-inkscape-font-specification:'Calibri, Normal';font-family:Calibri;font-weight:normal;font-style:normal;font-stretch:normal;font-variant:normal;font-size:20px;font-variant-ligatures:normal;font-variant-caps:normal;font-variant-numeric:normal;font-variant-east-asian:normal;fill:#000000">
<tspan id="Item2-tspan" x="" y="">Item2</tspan>
</text>
</g>
</g>    

This is the XSLT I am using

XSLT

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:svg="http://www.w3.org/2000/svg"
    xmlns="http://www.w3.org/2000/svg"
    exclude-result-prefixes="svg"
    version="1.0">
<xsl:output method="xml" encoding="utf-8" omit-xml-declaration="yes" indent="yes"/>
  
  <xsl:strip-space elements="*"/>
  
  <xsl:template match="svg:g[@id[starts-with(., 'Item')]]">
    <xsl:copy>
      <xsl:apply-templates select="@* | node()"/>
      <xsl:variable name="id" select="substring-before(@id, '-')"/>
      <text x="" y="" id="{$id}-text" style="-inkscape-font-specification:'Calibri, Normal';font-family:Calibri;font-weight:normal;font-style:normal;font-stretch:normal;font-variant:normal;font-size:20px;font-variant-ligatures:normal;font-variant-caps:normal;font-variant-numeric:normal;font-variant-east-asian:normal;fill:#000000">
        <tspan id="{$id}-tspan" x="" y="">
          <xsl:value-of select="$id"/>
        </tspan>
      </text>
    </xsl:copy>
  </xsl:template>
 
    <xsl:template name="string-replace-all">
        <xsl:param name="text" />
        <xsl:param name="replace" />
        <xsl:param name="by" />
        <xsl:choose>
            <xsl:when test="contains($text, $replace)">
                <xsl:value-of select="substring-before($text,$replace)" />
                <xsl:value-of select="$by" />
                <xsl:call-template name="string-replace-all">
                    <xsl:with-param name="text"
                        select="substring-after($text,$replace)" />
                    <xsl:with-param name="replace" select="$replace" />
                    <xsl:with-param name="by" select="$by" />
                </xsl:call-template>
            </xsl:when>
            <xsl:otherwise>
                <xsl:value-of select="$text" />
            </xsl:otherwise>
        </xsl:choose>
    </xsl:template>

    <!-- template call -->

    <xsl:variable name="result">
        <xsl:call-template name="string-replace-all">
            <xsl:with-param name="text" select="$text" />
            <xsl:with-param name="replace" select="'Item'" />
            <xsl:with-param name="by" select="''" />
        </xsl:call-template>
    </xsl:variable>
  
  
 <xsl:template match="processing-instruction('xml-stylesheet')"/>
 
  <xsl:template match="@* | node()">
    <xsl:copy>
      <xsl:apply-templates select="@* | node()"/>
    </xsl:copy>
  </xsl:template>

</xsl:stylesheet>

Finally, this is the result I would like to have:

Wanted SVG

<g id="symbols-svg">
<g id="Item1-svg" transform="translate(105, 210)">
<path d="M-5 0a5 5 0 1 0 10 0 5 5 0 1 0-10 0Z" stroke="rgb(200, 200, 200)" id="Item1" style="fill: rgb(0, 15, 60); stroke-width: 1; fill-opacity: 0.9; stroke-opacity: 0.5; stroke-linejoin: miter; stroke-linecap: butt; stroke: rgb(0, 50, 100);">
</path>
<text x="" y="" id="Item1-text" style="-inkscape-font-specification:'Calibri, Normal';font-family:Calibri;font-weight:normal;font-style:normal;font-stretch:normal;font-variant:normal;font-size:20px;font-variant-ligatures:normal;font-variant-caps:normal;font-variant-numeric:normal;font-variant-east-asian:normal;fill:#000000">
<tspan id="Item1-tspan" x="" y="">1</tspan>
</text>
</g>
<g id="Item2-svg" transform="translate(250, 90)">
<path d="M-5 0a5 5 0 1 0 10 0 5 5 0 1 0-10 0Z" stroke="rgb(200, 200, 200)" id="Item2" style="fill: rgb(0, 15, 60); stroke-width: 1; fill-opacity: 0.9; stroke-opacity: 0.5; stroke-linejoin: miter; stroke-linecap: butt; stroke: rgb(0, 50, 100);">
</path>
<text x="" y="" id="Item2-text" style="-inkscape-font-specification:'Calibri, Normal';font-family:Calibri;font-weight:normal;font-style:normal;font-stretch:normal;font-variant:normal;font-size:20px;font-variant-ligatures:normal;font-variant-caps:normal;font-variant-numeric:normal;font-variant-east-asian:normal;fill:#000000">
<tspan id="Item2-tspan" x="" y="">2</tspan>
</text>
</g>
</g>

With <xsl:with-param name="text" select="$text" />(both CreateObject("MSXML2.DOMDocument.3.0") and "MSXML2.DOMDocument.6.0") I get error '-2147467259 (80004005)' A reference to variable or parameter 'text' cannot be resolved. The variable or parameter may not be defined, or it may not be in scope.

With <xsl:with-param name="text" select="'Item'" /> nothing happens in both cases. Nor does it with <xsl:with-param name="text" select="'{Item}'" />.

I also tried nesting as per below (it may look like blasphemy to experts)

  <xsl:template match="svg:tspan[@id[starts-with(., 'Item')]]">
    <xsl:variable name="result">
        <xsl:call-template name="string-replace-all">
            <xsl:with-param name="text" select="'Item'" />
            <xsl:with-param name="replace" select="'Item'" />
            <xsl:with-param name="by" select="''" />
        </xsl:call-template>
    </xsl:variable>
  </xsl:template>

I cannot think of any more combinations (apart of course from the correct one...).

Answer 1

To remove a single instance of the text Item from a string $s, you can use this expression:

concat(substring-before($s, 'Item'), substring-after($s, 'Item'))

That assumes that $s does contain the string Item (substring-before and substring-after will return an empty string if the substring is not found), but if computation takes place in a template whose match expression checks that the text Item is indeed there, then that's a safe assumption.

In your example the text Item is always at the start of the string, and your template's match checks for that case. So you could just use:

substring-after(., 'Item')

EDIT: adding a full stylesheet:

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">

  <!-- template to trim "Item" from the start of every text node -->
  <xsl:template match="text()[starts-with(., 'Item')]">
    <xsl:value-of select="substring-after(., 'Item')"/>
  </xsl:template>

  <!-- identity template copies everything else unchanged -->
  <xsl:template match="@* | node()">
    <xsl:copy>
      <xsl:apply-templates select="@* | node()"/>
    </xsl:copy>
  </xsl:template>

</xsl:stylesheet>

View as XSLT Fiddle

Answer 2

I'm adding this as another "answer" although it's not a proposed solution to your problem (unlike my other answer which is a proposed solution).

I just want to point out a couple of problems with the example code you've posted, because I think they point to a misunderstanding of how XSLT works, and I think they show you need to read up on the basics of XSLT, otherwise you're reduced to copy-and-paste of other people's code, without understanding it, in the hope that you might get lucky with some random combination.

I hope this is helpful!

This variable assignment has two problems:

<xsl:variable name="result">
    <xsl:call-template name="string-replace-all">
        <xsl:with-param name="text" select="$text" />
        <xsl:with-param name="replace" select="'Item'" />
        <xsl:with-param name="by" select="''" />
    </xsl:call-template>
</xsl:variable>

Firstly, there's an illegal reference to a variable called $text. Because the <xsl:variable name="result"> is a child of the xsl:stylesheet element, that makes it a "top-level" or "global" variable. Effectively, such variables are calculated first (and once only!) when the stylesheet is processed, and within such a variable assignment, the only variables which can be referred to are other top-level variables or stylesheet parameters. You've referred to a variable called $text but there's no top-level variable $text.

Secondly, because $result is a top-level variable, that means it's visible to code anywhere else in the stylesheet; its value can be accessed anywhere by referring to the variable name $result. But in fact there are no references to $result anywhere in the stylesheet. That means this variable assignment is "dead code"; it does nothing. Typically, a stylesheet processor would ignore this code altogether, and should probably even warn you, because "dead code" while harmless in itself is generally a sign that the programmer has made a mistake.

You also mention another attempt:

<xsl:template match="svg:tspan[@id[starts-with(., 'Item')]]">
   <xsl:variable name="result">
      <xsl:call-template name="string-replace-all">
          <xsl:with-param name="text" select="'Item'" />
          <xsl:with-param name="replace" select="'Item'" />
          <xsl:with-param name="by" select="''" />
      </xsl:call-template>
   </xsl:variable>
</xsl:template>

This template will match tspan elements whose id starts with 'Item', but what effect will the template have? It does not produce any output, so its effect will be to "eat" those tspan elements. Note that again, the $result variable is "dead code"; a variable has a value assigned but then the variable is not actually used for anything. Here, because the variable is defined within a template, the variable is visible only to following siblings (i.e. it can be referred to only within XSLT elements which follow it at the same level). But this variable assignment is the last statement in the template, so there's no way that anything can refer to it.