Excel VBA Reading an XML file into a string runs out of characters

Question

I am currently reading from an XML file into a string using the following code:

'Target Path
sFileName = ThisWorkbook.Path & "\Assembly\Alarms.xml"
iFileNum = FreeFile

'Open File
Open sFileName For Input As iFileNum

'Read Text
Do Until EOF(iFileNum)
    Line Input #iFileNum, sBuf
    sTemp = sTemp & sBuf & vbCrLf
Loop

'Close File
Close iFileNum

The issue that I hit when doing this is a size constraint on the string. If I print out sTemp and select it all I get 32,767 characters.

I am pushing it into a string so I can perform a find/replace on specific line-items. Is there another (better) way to read from my XML and find/replace without running into this issue? Any suggestions would be appreciated.

<?xml version="1.0" encoding="UTF-8"?>
<alarms version="1.0" product="{E44CB020-C21D-11D3-8A3F-0010A4EF3494}" id="Alarms">
    <alarm history-size="10000" display-name="[ALARM]" hold-time="250" max-update-rate="1.00" embedded-server-update-rate="1.00" silence-tag="" remote-silence-exp="" remote-ack-all-exp="" status-reset-tag="" remote-status-reset-exp="" close-display-tag="" remote-close-display-exp="" use-alarm-identifier="false">
        <triggers>
            <trigger id="T1" type="bit" ack-all-value="999" use-ack-all="true" ack-tag="{[##PLCNAME]PVAlmAckCmd[0]}" exp="{[##PLCNAME]PVArrayAlarm[0],L1}" message-tag="" message-handshake-exp="" message-notification-tag="" remote-ack-exp="" remote-ack-handshake-tag="" label="Label1" handshake-tag=""/>
            <trigger id="T2" type="bit" ack-all-value="999" use-ack-all="true" ack-tag="{[##PLCNAME]PVAlmAckCmd[1]}" exp="{[##PLCNAME]PVArrayAlarm[1],L1}" message-tag="" message-handshake-exp="" message-notification-tag="" remote-ack-exp="" remote-ack-handshake-tag="" label="Label2" handshake-tag=""/>
            <trigger id="T3" type="bit" ack-all-value="999" use-ack-all="true" ack-tag="{[##PLCNAME]PVAlmAckCmd[2]}" exp="{[##PLCNAME]PVArrayAlarm[2],L1}" message-tag="" message-handshake-exp="" message-notification-tag="" remote-ack-exp="" remote-ack-handshake-tag="" label="Label3" handshake-tag=""/>
            <trigger id="T4" type="bit" ack-all-value="999" use-ack-all="true" ack-tag="{[##PLCNAME]PVAlmAckCmd[3]}" exp="{[##PLCNAME]PVArrayAlarm[3],L1}" message-tag="" message-handshake-exp="" message-notification-tag="" remote-ack-exp="" remote-ack-handshake-tag="" label="Label4" handshake-tag=""/>
            <trigger id="T5" type="bit" ack-all-value="999" use-ack-all="true" ack-tag="{[##PLCNAME]PVAlmAckCmd[4]}" exp="{[##PLCNAME]PVArrayAlarm[4],L1}" message-tag="" message-handshake-exp="" message-notification-tag="" remote-ack-exp="" remote-ack-handshake-tag="" label="Label5" handshake-tag=""/>
            <trigger id="T6" type="bit" ack-all-value="999" use-ack-all="true" ack-tag="{[##PLCNAME]PVAlmAckCmd[5]}" exp="{[##PLCNAME]PVArrayAlarm[5],L1}" message-tag="" message-handshake-exp="" message-notification-tag="" remote-ack-exp="" remote-ack-handshake-tag="" label="Label6" handshake-tag=""/>
            <trigger id="T7" type="bit" ack-all-value="999" use-ack-all="true" ack-tag="{[##PLCNAME]PVAlmAckCmd[6]}" exp="{[##PLCNAME]PVArrayAlarm[6],L1}" message-tag="" message-handshake-exp="" message-notification-tag="" remote-ack-exp="" remote-ack-handshake-tag="" label="Label7" handshake-tag=""/>
            <trigger id="T8" type="bit" ack-all-value="999" use-ack-all="true" ack-tag="{[##PLCNAME]PVAlmAckCmd[7]}" exp="{[##PLCNAME]PVArrayAlarm[7],L1}" message-tag="" message-handshake-exp="" message-notification-tag="" remote-ack-exp="" remote-ack-handshake-tag="" label="Label8" handshake-tag=""/>
            <trigger id="T9" type="bit" ack-all-value="999" use-ack-all="true" ack-tag="{[##PLCNAME]PVAlmAckCmd[8]}" exp="{[##PLCNAME]PVArrayAlarm[8],L1}" message-tag="" message-handshake-exp="" message-notification-tag="" remote-ack-exp="" remote-ack-handshake-tag="" label="Label9" handshake-tag=""/>
            <trigger id="T10" type="bit" ack-all-value="999" use-ack-all="true" ack-tag="{[##PLCNAME]PVAlmAckCmd[9]}" exp="{[##PLCNAME]PVArrayAlarm[9],L1}" message-tag="" message-handshake-exp="" message-notification-tag="" remote-ack-exp="" remote-ack-handshake-tag="" label="Label10" handshake-tag=""/>
            <trigger id="T11" type="bit" ack-all-value="999" use-ack-all="true" ack-tag="{[##PLCNAME]PVAlmAckCmd[10]}" exp="{[##PLCNAME]PVArrayAlarm[10],L1}" message-tag="" message-handshake-exp="" message-notification-tag="" remote-ack-exp="" remote-ack-handshake-tag="" label="Label11" handshake-tag=""/>
            <trigger id="T12" type="bit" ack-all-value="999" use-ack-all="true" ack-tag="{[##PLCNAME]PVAlmAckCmd[11]}" exp="{[##PLCNAME]PVArrayAlarm[11],L1}" message-tag="" message-handshake-exp="" message-notification-tag="" remote-ack-exp="" remote-ack-handshake-tag="" label="Label12" handshake-tag=""/>
            <trigger id="T13" type="bit" ack-all-value="999" use-ack-all="true" ack-tag="{[##PLCNAME]PVAlmAckCmd[12]}" exp="{[##PLCNAME]PVArrayAlarm[12],L1}" message-tag="" message-handshake-exp="" message-notification-tag="" remote-ack-exp="" remote-ack-handshake-tag="" label="Label13" handshake-tag=""/>
            <trigger id="T14" type="bit" ack-all-value="999" use-ack-all="true" ack-tag="{[##PLCNAME]PVAlmAckCmd[13]}" exp="{[##PLCNAME]PVArrayAlarm[13],L1}" message-tag="" message-handshake-exp="" message-notification-tag="" remote-ack-exp="" remote-ack-handshake-tag="" label="Label14" handshake-tag=""/>
            <trigger id="T15" type="bit" ack-all-value="999" use-ack-all="true" ack-tag="{[##PLCNAME]PVAlmAckCmd[14]}" exp="{[##PLCNAME]PVArrayAlarm[14],L1}" message-tag="" message-handshake-exp="" message-notification-tag="" remote-ack-exp="" remote-ack-handshake-tag="" label="Label15" handshake-tag=""/>
            <trigger id="T16" type="bit" ack-all-value="999" use-ack-all="true" ack-tag="{[##PLCNAME]PVAlmAckCmd[15]}" exp="{[##PLCNAME]PVArrayAlarm[15],L1}" message-tag="" message-handshake-exp="" message-notification-tag="" remote-ack-exp="" remote-ack-handshake-tag="" label="Label16" handshake-tag=""/>
            <trigger id="T17" type="bit" ack-all-value="999" use-ack-all="true" ack-tag="{[##PLCNAME]PVAlmAckCmd[16]}" exp="{[##PLCNAME]PVArrayAlarm[16],L1}" message-tag="" message-handshake-exp="" message-notification-tag="" remote-ack-exp="" remote-ack-handshake-tag="" label="Label17" handshake-tag=""/>
            <trigger id="T18" type="bit" ack-all-value="999" use-ack-all="true" ack-tag="{[##PLCNAME]PVAlmAckCmd[17]}" exp="{[##PLCNAME]PVArrayAlarm[17],L1}" message-tag="" message-handshake-exp="" message-notification-tag="" remote-ack-exp="" remote-ack-handshake-tag="" label="Label18" handshake-tag=""/>
            <trigger id="T19" type="bit" ack-all-value="999" use-ack-all="true" ack-tag="{[##PLCNAME]PVAlmAckCmd[18]}" exp="{[##PLCNAME]PVArrayAlarm[18],L1}" message-tag="" message-handshake-exp="" message-notification-tag="" remote-ack-exp="" remote-ack-handshake-tag="" label="Label19" handshake-tag=""/>
            <trigger id="T20" type="bit" ack-all-value="999" use-ack-all="true" ack-tag="{[##PLCNAME]PVAlmAckCmd[19]}" exp="{[##PLCNAME]PVArrayAlarm[19],L1}" message-tag="" message-handshake-exp="" message-notification-tag="" remote-ack-exp="" remote-ack-handshake-tag="" label="Label20" handshake-tag=""/>
            <trigger id="T21" type="bit" ack-all-value="999" use-ack-all="true" ack-tag="{[##PLCNAME]PVAlmAckCmd[20]}" exp="{[##PLCNAME]PVArrayAlarm[20],L1}" message-tag="" message-handshake-exp="" message-notification-tag="" remote-ack-exp="" remote-ack-handshake-tag="" label="Label21" handshake-tag=""/>
            <trigger id="T22" type="bit" ack-all-value="999" use-ack-all="true" ack-tag="{[##PLCNAME]PVAlmAckCmd[21]}" exp="{[##PLCNAME]PVArrayAlarm[21],L1}" message-tag="" message-handshake-exp="" message-notification-tag="" remote-ack-exp="" remote-ack-handshake-tag="" label="Label22" handshake-tag=""/>
            <trigger id="T23" type="bit" ack-all-value="999" use-ack-all="true" ack-tag="{[##PLCNAME]PVAlmAckCmd[22]}" exp="{[##PLCNAME]PVArrayAlarm[22],L1}" message-tag="" message-handshake-exp="" message-notification-tag="" remote-ack-exp="" remote-ack-handshake-tag="" label="Label23" handshake-tag=""/>
            <trigger id="T24" type="bit" ack-all-value="999" use-ack-all="true" ack-tag="{[##PLCNAME]PVAlmAckCmd[23]}" exp="{[##PLCNAME]PVArrayAlarm[23],L1}" message-tag="" message-handshake-exp="" message-notification-tag="" remote-ack-exp="" remote-ack-handshake-tag="" label="Label24" handshake-tag=""/>
            <trigger id="T25" type="bit" ack-all-value="999" use-ack-all="true" ack-tag="{[##PLCNAME]PVAlmAckCmd[24]}" exp="{[##PLCNAME]PVArrayAlarm[24],L1}" message-tag="" message-handshake-exp="" message-notification-tag="" remote-ack-exp="" remote-ack-handshake-tag="" label="Label25" handshake-tag=""/>
            <trigger id="T26" type="bit" ack-all-value="999" use-ack-all="true" ack-tag="{[##PLCNAME]PVAlmAckCmd[25]}" exp="{[##PLCNAME]PVArrayAlarm[25],L1}" message-tag="" message-handshake-exp="" message-notification-tag="" remote-ack-exp="" remote-ack-handshake-tag="" label="Label26" handshake-tag=""/>
            <trigger id="T27" type="bit" ack-all-value="999" use-ack-all="true" ack-tag="{[##PLCNAME]PVAlmAckCmd[26]}" exp="{[##PLCNAME]PVArrayAlarm[26],L1}" message-tag="" message-handshake-exp="" message-notification-tag="" remote-ack-exp="" remote-ack-handshake-tag="" label="Label27" handshake-tag=""/>
            <trigger id="T28" type="bit" ack-all-value="999" use-ack-all="true" ack-tag="{[##PLCNAME]PVAlmAckCmd[27]}" exp="{[##PLCNAME]PVArrayAlarm[27],L1}" message-tag="" message-handshake-exp="" message-notification-tag="" remote-ack-exp="" remote-ack-handshake-tag="" label="Label28" handshake-tag=""/>
            <trigger id="T29" type="bit" ack-all-value="999" use-ack-all="true" ack-tag="{[##PLCNAME]PVAlmAckCmd[28]}" exp="{[##PLCNAME]PVArrayAlarm[28],L1}" message-tag="" message-handshake-exp="" message-notification-tag="" remote-ack-exp="" remote-ack-handshake-tag="" label="Label29" handshake-tag=""/>
            <trigger id="T30" type="bit" ack-all-value="999" use-ack-all="true" ack-tag="{[##PLCNAME]PVAlmAckCmd[29]}" exp="{[##PLCNAME]PVArrayAlarm[29],L1}" message-tag="" message-handshake-exp="" message-notification-tag="" remote-ack-exp="" remote-ack-handshake-tag="" label="Label30" handshake-tag=""/>
        </triggers>
        <messages>
            <message id="M1" trigger-value="1" identifier="0" trigger="#T1" backcolor="#800000" forecolor="#FFFFFF" audio="false" display="true" print="false" message-to-tag="false" text="##SPARE_ALARM[0].0"/>
            <message id="M2" trigger-value="2" identifier="0" trigger="#T1" backcolor="#800000" forecolor="#FFFFFF" audio="false" display="true" print="false" message-to-tag="false" text="##SPARE_ALARM[0].1"/>
            <message id="M3" trigger-value="3" identifier="0" trigger="#T1" backcolor="#800000" forecolor="#FFFFFF" audio="false" display="true" print="false" message-to-tag="false" text="##SPARE_ALARM[0].2"/>
            <message id="M4" trigger-value="4" identifier="0" trigger="#T1" backcolor="#800000" forecolor="#FFFFFF" audio="false" display="true" print="false" message-to-tag="false" text="##SPARE_ALARM[0].3"/>
            <message id="M5" trigger-value="5" identifier="0" trigger="#T1" backcolor="#800000" forecolor="#FFFFFF" audio="false" display="true" print="false" message-to-tag="false" text="##SPARE_ALARM[0].4"/>
            <message id="M6" trigger-value="6" identifier="0" trigger="#T1" backcolor="#800000" forecolor="#FFFFFF" audio="false" display="true" print="false" message-to-tag="false" text="##SPARE_ALARM[0].5"/>
            <message id="M7" trigger-value="7" identifier="0" trigger="#T1" backcolor="#800000" forecolor="#FFFFFF" audio="false" display="true" print="false" message-to-tag="false" text="##SPARE_ALARM[0].6"/>
            <message id="M8" trigger-value="8" identifier="0" trigger="#T1" backcolor="#800000" forecolor="#FFFFFF" audio="false" display="true" print="false" message-to-tag="false" text="##SPARE_ALARM[0].7"/>
            <message id="M9" trigger-value="9" identifier="0" trigger="#T1" backcolor="#800000" forecolor="#FFFFFF" audio="false" display="true" print="false" message-to-tag="false" text="##SPARE_ALARM[0].8"/>
            <message id="M10" trigger-value="10" identifier="0" trigger="#T1" backcolor="#800000" forecolor="#FFFFFF" audio="false" display="true" print="false" message-to-tag="false" text="##SPARE_ALARM[0].9"/>
            <message id="M11" trigger-value="11" identifier="0" trigger="#T1" backcolor="#800000" forecolor="#FFFFFF" audio="false" display="true" print="false" message-to-tag="false" text="##SPARE_ALARM[0].10"/>
            <message id="M12" trigger-value="12" identifier="0" trigger="#T1" backcolor="#800000" forecolor="#FFFFFF" audio="false" display="true" print="false" message-to-tag="false" text="##SPARE_ALARM[0].11"/>

The above is an example of my XML. I pretty much want to change the ##Tags. The ##SPARE_ALARM array goes up to 30 DINTS. Each one is an individual alarm that I am trying to update the description for.

Using find and replace on a raw XML string is a recipe for disaster. See [How to parse XML using vba](https://stackoverflow.com/q/11305/4088852). — Comintern, Jan 25 '19 at 19:18
Also: [how to parse XML with regex](https://stackoverflow.com/a/1732454/1188513), in case you're tempted ;-) — Mathieu Guindon, Jan 25 '19 at 19:32
@Comintern To be fair the OP didn't say what he was find/replacing or even that it involves tags. It could be something as unique and harmless (barring stupid match mistakes) as replace all instances of *My Company, LLC* with *My Company, Inc.*. — Mark Balhoff, Jan 25 '19 at 19:34
@MarkBalhoff To be fair, if you get into the habit of assuming that you won't have a node named for what you're replacing, you *will* run into problems some day. XML is structured. — Comintern, Jan 25 '19 at 19:38
@MarkBalhoff, even with such a change, OP doen't need to read XML into a string. You can easily change node values or node names or anything else about an XML with [XSLT](https://www.w3.org/Style/XSL/) which VBA can run with MSXML. — Parfait, Jan 25 '19 at 19:38
@MathieuGuindon I agree with all three of the above comments. I'd never recommend it. I probably should have made that disclaimer clear. There are better ways (RubberDuck a great one) and bad habits can cause problems later. Just wanted to point out it's not unthinkable for a one time task if you know your XML well and your search match is obscure relative to the tags. I have nothing but respect for RubberDuck and its dev team despite admittedly (for reasons unbeknownst to me) never having tried to use. — Mark Balhoff, Jan 25 '19 at 19:51
So if I am stuck with using Excel VBA for my company the best approach would be to change from using a string to using XMLDOC and iterating through children? Wouldn't that take longer than just find/replacing on a string because I have to jump through a bunch of loops until I find the words I need to replace? — snovosad54, Jan 25 '19 at 19:58
@slnd54 you don't iterate through children, you write an xpath query, and let the MSXML engine do the hard work for you - hunch says it's going to be much more efficient than find/replace in a string that's too large to fit a `String`. See [this related Q&A](https://stackoverflow.com/a/30827849/1188513)... it's literally *made for this*. — Mathieu Guindon, Jan 25 '19 at 20:01
I feel like the first few sentences of that link says I should not be using that method. I am not really using the XML as an XML and really do want a blanket find/replace. I uploaded an example of a portion of my XML. Would you still recommend going down the xpath route? — snovosad54, Jan 25 '19 at 20:08
@slnd54 In any case it would be helpful to indicate whether you are searching to replace any matching string or only tokens within given nodes. - BTW MSXML offers the `.xml` property delivering the complete node hierarchy as string. — T.M., Jan 25 '19 at 20:09
I apologize for the question not being as concise as it could have been. I am replacing the ##SPARE_ALARM entry based on the array position. So ##SPARE_ALARM[0].0 would turn into something like MOTOR 1 FAULT or whatever my alarm description is while ##SPARE_ALARM[0].1 would be MOTOR 2 FAULT and so on. — snovosad54, Jan 25 '19 at 20:11
Keywords are "But if finding the text to change does depend on the XML context, I would write a little XSLT 2.0 program to do it" - as was already suggested above; I just provided the first link I found with an example of it, feel free to research for more =) ...XPath is just the name of the query syntax that you can use to easily fetch specific nodes without manually traversing anything. — Mathieu Guindon, Jan 25 '19 at 20:16

Parfait · Accepted Answer · 2019-01-26T16:49:12.587

Fundamentally, an XML should not be attributed as a text file but a special markup document that adheres to industry standards, specifically the W3C standards. Hence, to manipulate an XML document by finding and replacing node values should be done with the special-purpose, declarative language known as XSLT whose code is laid out in a special XML file.

XSLT can be run by itself via dedicated standalone software or interfaced via application layer (Java, PHP, Python, C#, VB, R) using special modules or libraries (not unlike the other popular special-purpose, also declarative language known as SQL). And VBA has such a library with MSXML.

Consider below process to change text attributes to needed MOTOR ## FAULT output which will be passed as parameter into VBA:

XSLT 1.0 (save as an .xsl file, a special .xml file to be loaded in VBA)

NOTE: XSLT will receive two parameters from VBA

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

  <!-- APP LAYER PARAM PLACEHOLDERS -->
  <xsl:param name="prefix"/>
  <xsl:param name="suffix"/>

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

  <xsl:template match="message[contains(@text,'##SPARE_ALARM[0]')]">
    <xsl:copy>
      <xsl:apply-templates select="@*"/>
      <xsl:attribute name="text">
          <xsl:value-of select="concat($prefix, count(preceding-sibling::*)+1, $suffix)"/>
      </xsl:attribute>
    </xsl:copy>
  </xsl:template>

</xsl:stylesheet>

Online demo

VBA (no For loops or If logic used and passes parameters to XSLT)

Sub XSLTransform()
On Error GoTo ErrHandle
    ' SELECT Microsoft XML, v## AS VBA REFERENCE
    Dim xmldoc As New MSXML2.DOMDocument, newDoc As New MSXML2.DOMDocument
    Dim xslDoc As New MSXML2.FreeThreadedDOMDocument
    Dim tmpl As Object, xslproc As Object

    ' LOAD XML AND XSL FILES
    xmldoc.async = False
    xmldoc.Load "C:\Path\To\Input.xml"

    xslDoc.async = False
    xslDoc.Load "C:\Path\To\XSLT\Script.xsl"

    ' INITIALIZE TEMPLATE AND PROCESSOR
    Set tmpl = CreateObject("MSXML2.XSLTemplate")
    tmpl.stylesheet = xslDoc

    Set xslproc = tmpl.createProcessor()

    ' TRANSFORM XML WITH PARAMS
    xslproc.input = xmldoc
    xslproc.addParameter "prefix", "MOTOR "
    xslproc.addParameter "suffix", " FAULT"
    xslproc.transform

    ' SAVE OUTPUT TO FILE
    newDoc.LoadXML xslproc.output
    newDoc.Save "C:\Path\To\Output.xml"

    MsgBox "Successfully transformed XML!", vbInformation
    Exit Sub

ExitHandle:
    Set xmldoc = Nothing: Set xslDoc = Nothing: Set newDoc = Nothing
    Set tmpl = Nothing: Set xslproc = Nothing
    Exit Sub

ErrHandle:
    MsgBox Err.Number & " - " & Err.Description, vbCritical
    Err.Raise xslDoc.parseError.ErrorCode, , xslDoc.parseError.reason
    Resume ExitHandle

End Sub

Output

<?xml version="1.0" encoding="utf-16"?>
<alarms version="1.0" product="{E44CB020-C21D-11D3-8A3F-0010A4EF3494}" id="Alarms">
  <alarm history-size="10000" display-name="[ALARM]" hold-time="250" max-update-rate="1.00" embedded-server-update-rate="1.00" silence-tag="" remote-silence-exp="" remote-ack-all-exp="" status-reset-tag="" remote-status-reset-exp="" close-display-tag="" remote-close-display-exp="" use-alarm-identifier="false">
    <triggers>
      <trigger id="T1" type="bit" ack-all-value="999" use-ack-all="true" ack-tag="{[##PLCNAME]PVAlmAckCmd[0]}" exp="{[##PLCNAME]PVArrayAlarm[0],L1}" message-tag="" message-handshake-exp="" message-notification-tag="" remote-ack-exp="" remote-ack-handshake-tag="" label="Label1" handshake-tag="" />
      <trigger id="T2" type="bit" ack-all-value="999" use-ack-all="true" ack-tag="{[##PLCNAME]PVAlmAckCmd[1]}" exp="{[##PLCNAME]PVArrayAlarm[1],L1}" message-tag="" message-handshake-exp="" message-notification-tag="" remote-ack-exp="" remote-ack-handshake-tag="" label="Label2" handshake-tag="" />
      <trigger id="T3" type="bit" ack-all-value="999" use-ack-all="true" ack-tag="{[##PLCNAME]PVAlmAckCmd[2]}" exp="{[##PLCNAME]PVArrayAlarm[2],L1}" message-tag="" message-handshake-exp="" message-notification-tag="" remote-ack-exp="" remote-ack-handshake-tag="" label="Label3" handshake-tag="" />
      <trigger id="T4" type="bit" ack-all-value="999" use-ack-all="true" ack-tag="{[##PLCNAME]PVAlmAckCmd[3]}" exp="{[##PLCNAME]PVArrayAlarm[3],L1}" message-tag="" message-handshake-exp="" message-notification-tag="" remote-ack-exp="" remote-ack-handshake-tag="" label="Label4" handshake-tag="" />
      <trigger id="T5" type="bit" ack-all-value="999" use-ack-all="true" ack-tag="{[##PLCNAME]PVAlmAckCmd[4]}" exp="{[##PLCNAME]PVArrayAlarm[4],L1}" message-tag="" message-handshake-exp="" message-notification-tag="" remote-ack-exp="" remote-ack-handshake-tag="" label="Label5" handshake-tag="" />
      <trigger id="T6" type="bit" ack-all-value="999" use-ack-all="true" ack-tag="{[##PLCNAME]PVAlmAckCmd[5]}" exp="{[##PLCNAME]PVArrayAlarm[5],L1}" message-tag="" message-handshake-exp="" message-notification-tag="" remote-ack-exp="" remote-ack-handshake-tag="" label="Label6" handshake-tag="" />
      <trigger id="T7" type="bit" ack-all-value="999" use-ack-all="true" ack-tag="{[##PLCNAME]PVAlmAckCmd[6]}" exp="{[##PLCNAME]PVArrayAlarm[6],L1}" message-tag="" message-handshake-exp="" message-notification-tag="" remote-ack-exp="" remote-ack-handshake-tag="" label="Label7" handshake-tag="" />
      <trigger id="T8" type="bit" ack-all-value="999" use-ack-all="true" ack-tag="{[##PLCNAME]PVAlmAckCmd[7]}" exp="{[##PLCNAME]PVArrayAlarm[7],L1}" message-tag="" message-handshake-exp="" message-notification-tag="" remote-ack-exp="" remote-ack-handshake-tag="" label="Label8" handshake-tag="" />
      <trigger id="T9" type="bit" ack-all-value="999" use-ack-all="true" ack-tag="{[##PLCNAME]PVAlmAckCmd[8]}" exp="{[##PLCNAME]PVArrayAlarm[8],L1}" message-tag="" message-handshake-exp="" message-notification-tag="" remote-ack-exp="" remote-ack-handshake-tag="" label="Label9" handshake-tag="" />
      <trigger id="T10" type="bit" ack-all-value="999" use-ack-all="true" ack-tag="{[##PLCNAME]PVAlmAckCmd[9]}" exp="{[##PLCNAME]PVArrayAlarm[9],L1}" message-tag="" message-handshake-exp="" message-notification-tag="" remote-ack-exp="" remote-ack-handshake-tag="" label="Label10" handshake-tag="" />
      <trigger id="T11" type="bit" ack-all-value="999" use-ack-all="true" ack-tag="{[##PLCNAME]PVAlmAckCmd[10]}" exp="{[##PLCNAME]PVArrayAlarm[10],L1}" message-tag="" message-handshake-exp="" message-notification-tag="" remote-ack-exp="" remote-ack-handshake-tag="" label="Label11" handshake-tag="" />
      <trigger id="T12" type="bit" ack-all-value="999" use-ack-all="true" ack-tag="{[##PLCNAME]PVAlmAckCmd[11]}" exp="{[##PLCNAME]PVArrayAlarm[11],L1}" message-tag="" message-handshake-exp="" message-notification-tag="" remote-ack-exp="" remote-ack-handshake-tag="" label="Label12" handshake-tag="" />
      <trigger id="T13" type="bit" ack-all-value="999" use-ack-all="true" ack-tag="{[##PLCNAME]PVAlmAckCmd[12]}" exp="{[##PLCNAME]PVArrayAlarm[12],L1}" message-tag="" message-handshake-exp="" message-notification-tag="" remote-ack-exp="" remote-ack-handshake-tag="" label="Label13" handshake-tag="" />
      <trigger id="T14" type="bit" ack-all-value="999" use-ack-all="true" ack-tag="{[##PLCNAME]PVAlmAckCmd[13]}" exp="{[##PLCNAME]PVArrayAlarm[13],L1}" message-tag="" message-handshake-exp="" message-notification-tag="" remote-ack-exp="" remote-ack-handshake-tag="" label="Label14" handshake-tag="" />
      <trigger id="T15" type="bit" ack-all-value="999" use-ack-all="true" ack-tag="{[##PLCNAME]PVAlmAckCmd[14]}" exp="{[##PLCNAME]PVArrayAlarm[14],L1}" message-tag="" message-handshake-exp="" message-notification-tag="" remote-ack-exp="" remote-ack-handshake-tag="" label="Label15" handshake-tag="" />
      <trigger id="T16" type="bit" ack-all-value="999" use-ack-all="true" ack-tag="{[##PLCNAME]PVAlmAckCmd[15]}" exp="{[##PLCNAME]PVArrayAlarm[15],L1}" message-tag="" message-handshake-exp="" message-notification-tag="" remote-ack-exp="" remote-ack-handshake-tag="" label="Label16" handshake-tag="" />
      <trigger id="T17" type="bit" ack-all-value="999" use-ack-all="true" ack-tag="{[##PLCNAME]PVAlmAckCmd[16]}" exp="{[##PLCNAME]PVArrayAlarm[16],L1}" message-tag="" message-handshake-exp="" message-notification-tag="" remote-ack-exp="" remote-ack-handshake-tag="" label="Label17" handshake-tag="" />
      <trigger id="T18" type="bit" ack-all-value="999" use-ack-all="true" ack-tag="{[##PLCNAME]PVAlmAckCmd[17]}" exp="{[##PLCNAME]PVArrayAlarm[17],L1}" message-tag="" message-handshake-exp="" message-notification-tag="" remote-ack-exp="" remote-ack-handshake-tag="" label="Label18" handshake-tag="" />
      <trigger id="T19" type="bit" ack-all-value="999" use-ack-all="true" ack-tag="{[##PLCNAME]PVAlmAckCmd[18]}" exp="{[##PLCNAME]PVArrayAlarm[18],L1}" message-tag="" message-handshake-exp="" message-notification-tag="" remote-ack-exp="" remote-ack-handshake-tag="" label="Label19" handshake-tag="" />
      <trigger id="T20" type="bit" ack-all-value="999" use-ack-all="true" ack-tag="{[##PLCNAME]PVAlmAckCmd[19]}" exp="{[##PLCNAME]PVArrayAlarm[19],L1}" message-tag="" message-handshake-exp="" message-notification-tag="" remote-ack-exp="" remote-ack-handshake-tag="" label="Label20" handshake-tag="" />
      <trigger id="T21" type="bit" ack-all-value="999" use-ack-all="true" ack-tag="{[##PLCNAME]PVAlmAckCmd[20]}" exp="{[##PLCNAME]PVArrayAlarm[20],L1}" message-tag="" message-handshake-exp="" message-notification-tag="" remote-ack-exp="" remote-ack-handshake-tag="" label="Label21" handshake-tag="" />
      <trigger id="T22" type="bit" ack-all-value="999" use-ack-all="true" ack-tag="{[##PLCNAME]PVAlmAckCmd[21]}" exp="{[##PLCNAME]PVArrayAlarm[21],L1}" message-tag="" message-handshake-exp="" message-notification-tag="" remote-ack-exp="" remote-ack-handshake-tag="" label="Label22" handshake-tag="" />
      <trigger id="T23" type="bit" ack-all-value="999" use-ack-all="true" ack-tag="{[##PLCNAME]PVAlmAckCmd[22]}" exp="{[##PLCNAME]PVArrayAlarm[22],L1}" message-tag="" message-handshake-exp="" message-notification-tag="" remote-ack-exp="" remote-ack-handshake-tag="" label="Label23" handshake-tag="" />
      <trigger id="T24" type="bit" ack-all-value="999" use-ack-all="true" ack-tag="{[##PLCNAME]PVAlmAckCmd[23]}" exp="{[##PLCNAME]PVArrayAlarm[23],L1}" message-tag="" message-handshake-exp="" message-notification-tag="" remote-ack-exp="" remote-ack-handshake-tag="" label="Label24" handshake-tag="" />
      <trigger id="T25" type="bit" ack-all-value="999" use-ack-all="true" ack-tag="{[##PLCNAME]PVAlmAckCmd[24]}" exp="{[##PLCNAME]PVArrayAlarm[24],L1}" message-tag="" message-handshake-exp="" message-notification-tag="" remote-ack-exp="" remote-ack-handshake-tag="" label="Label25" handshake-tag="" />
      <trigger id="T26" type="bit" ack-all-value="999" use-ack-all="true" ack-tag="{[##PLCNAME]PVAlmAckCmd[25]}" exp="{[##PLCNAME]PVArrayAlarm[25],L1}" message-tag="" message-handshake-exp="" message-notification-tag="" remote-ack-exp="" remote-ack-handshake-tag="" label="Label26" handshake-tag="" />
      <trigger id="T27" type="bit" ack-all-value="999" use-ack-all="true" ack-tag="{[##PLCNAME]PVAlmAckCmd[26]}" exp="{[##PLCNAME]PVArrayAlarm[26],L1}" message-tag="" message-handshake-exp="" message-notification-tag="" remote-ack-exp="" remote-ack-handshake-tag="" label="Label27" handshake-tag="" />
      <trigger id="T28" type="bit" ack-all-value="999" use-ack-all="true" ack-tag="{[##PLCNAME]PVAlmAckCmd[27]}" exp="{[##PLCNAME]PVArrayAlarm[27],L1}" message-tag="" message-handshake-exp="" message-notification-tag="" remote-ack-exp="" remote-ack-handshake-tag="" label="Label28" handshake-tag="" />
      <trigger id="T29" type="bit" ack-all-value="999" use-ack-all="true" ack-tag="{[##PLCNAME]PVAlmAckCmd[28]}" exp="{[##PLCNAME]PVArrayAlarm[28],L1}" message-tag="" message-handshake-exp="" message-notification-tag="" remote-ack-exp="" remote-ack-handshake-tag="" label="Label29" handshake-tag="" />
      <trigger id="T30" type="bit" ack-all-value="999" use-ack-all="true" ack-tag="{[##PLCNAME]PVAlmAckCmd[29]}" exp="{[##PLCNAME]PVArrayAlarm[29],L1}" message-tag="" message-handshake-exp="" message-notification-tag="" remote-ack-exp="" remote-ack-handshake-tag="" label="Label30" handshake-tag="" />
    </triggers>
    <messages>
      <message id="M1" trigger-value="1" identifier="0" trigger="#T1" backcolor="#800000" forecolor="#FFFFFF" audio="false" display="true" print="false" message-to-tag="false" text="MOTOR 1 FAULT" />
      <message id="M2" trigger-value="2" identifier="0" trigger="#T1" backcolor="#800000" forecolor="#FFFFFF" audio="false" display="true" print="false" message-to-tag="false" text="MOTOR 2 FAULT" />
      <message id="M3" trigger-value="3" identifier="0" trigger="#T1" backcolor="#800000" forecolor="#FFFFFF" audio="false" display="true" print="false" message-to-tag="false" text="MOTOR 3 FAULT" />
      <message id="M4" trigger-value="4" identifier="0" trigger="#T1" backcolor="#800000" forecolor="#FFFFFF" audio="false" display="true" print="false" message-to-tag="false" text="MOTOR 4 FAULT" />
      <message id="M5" trigger-value="5" identifier="0" trigger="#T1" backcolor="#800000" forecolor="#FFFFFF" audio="false" display="true" print="false" message-to-tag="false" text="MOTOR 5 FAULT" />
      <message id="M6" trigger-value="6" identifier="0" trigger="#T1" backcolor="#800000" forecolor="#FFFFFF" audio="false" display="true" print="false" message-to-tag="false" text="MOTOR 6 FAULT" />
      <message id="M7" trigger-value="7" identifier="0" trigger="#T1" backcolor="#800000" forecolor="#FFFFFF" audio="false" display="true" print="false" message-to-tag="false" text="MOTOR 7 FAULT" />
      <message id="M8" trigger-value="8" identifier="0" trigger="#T1" backcolor="#800000" forecolor="#FFFFFF" audio="false" display="true" print="false" message-to-tag="false" text="MOTOR 8 FAULT" />
      <message id="M9" trigger-value="9" identifier="0" trigger="#T1" backcolor="#800000" forecolor="#FFFFFF" audio="false" display="true" print="false" message-to-tag="false" text="MOTOR 9 FAULT" />
      <message id="M10" trigger-value="10" identifier="0" trigger="#T1" backcolor="#800000" forecolor="#FFFFFF" audio="false" display="true" print="false" message-to-tag="false" text="MOTOR 10 FAULT" />
      <message id="M11" trigger-value="11" identifier="0" trigger="#T1" backcolor="#800000" forecolor="#FFFFFF" audio="false" display="true" print="false" message-to-tag="false" text="MOTOR 11 FAULT" />
    </messages>
  </alarm>
</alarms>

Is it possible for the XSL file to have variable text? I am doing this through an Excel VBA script to reference alarm data stored within the excel sheet itself. — snovosad54, Jan 25 '19 at 20:33
Fine answer demonstrating XSLT efficience & nice application of `preceding-sibling` +1) — T.M., Jan 25 '19 at 20:34
@slnd54 you could *generate* the XSL file, then load it with MSXML ;-) — Mathieu Guindon, Jan 25 '19 at 20:38
@slnd54 Using *early binding* here as well as possibly the current version 6.0 (with full support of XPath 1.0), I'd suggest to declare the document type explicitly as `DOMDocument60` or `MSXML2.DOMDocument60`; AFAIK `DOMDocument` is related to the prior version 3.0 by default. — T.M., Jan 25 '19 at 21:09
@slnd54 ... You can pass parameters to an XSLT! See updated XSLT and VBA. XSLT will now receive two parameters, *prefix* and *suffix* and its values *MOTOR* and *FAULT* are passed in from VBA. — Parfait, Jan 25 '19 at 21:57
And thank you for this question! This is the first time I ever passed parameters from VBA to XSLT, having done so with Java, PHP, and Python to XSLT! — Parfait, Jan 25 '19 at 21:58

score 1 · Answer 2 · answered Jan 26 '19 at 00:11

If you had variable text which has no pattern, you could just read from the Excel sheet:

Sub TransformXML()
    Const xmlPath = "C:\Path\To\Alarms.xml"

    Dim xDoc As New MSXML2.DOMDocument60
    Dim loaded As Boolean
    xDoc.async = False
    loaded = xDoc.Load(xmlPath)
    If Not loaded Then
        MsgBox xDoc.parseError.reason
        Exit Sub
    End If
    'Assume that you have the old values in A2:A13 and the new values in B2:B13
    Dim transformRange As Range, cell As Range
    Set transformRange = Sheet1.Range("A2:B13")
    For Each cell In transformRange.Columns(1).Cells
        Dim xp As String
        xp = "/alarms/alarm/messages/message[@text='" & cell.Value2 & "']"
        Dim success As Boolean
        success = SetAttrAtPath(xDoc, xp, "text", cell.Offset(0, 1).Value2)
    Next cell

    xDoc.Save xmlPath
End Sub

Function SetAttrAtPath(loadedXMLDoc As DOMDocument60, _
                   elementXPath As String, _
                   attrName As String, _
                   newAttrValue As String) As Boolean
    Dim xNodes As IXMLDOMNodeList
    'Find all elements that fit the path i.e. have that element structure and attribute
    Set xNodes = loadedXMLDoc.SelectNodes(elementXPath)
    If xNodes.Length > 0 Then
        Dim elCount As Long
        For elCount = 0 To xNodes.Length - 1
            'set the attribute to have the new value
            xNodes.Item(elCount).Attributes.getNamedItem(attrName).NodeValue = newAttrValue
        Next elCount
        SetAttrAtPath = True
        Exit Function
    End If
End Function

Excel VBA Reading an XML file into a string runs out of characters

2 Answers2

Linked