时间戳

首页 > 解决方案 > VBA：查找和替换 XML 节点

xml - VBA：查找和替换 XML 节点

问题描述

我有一个定义进程和一些其他参数的 XML 文件，它们将在启动时由 CAN 控制器加载。我将使用它作为不同控制器的模板，因此我需要操作 XML 某些部分中的项目。特别是，我将定义不同的子组件并修改一些现有的子组件。而且我必须使用 Excel VBA 来处理所有这些东西：最终会有十分之一，如果不是数百个具有不同定义的控制器。

我设法绕过了克隆和附加新节点的过程，但看起来我的大脑拒绝掌握 XML 解析、编辑等背后的概念。

我现在手头的问题是如何查找和替换子组件树的子节点（或者如果一个或多个属性已经满足某些条件：主要是磁盘上的路径）。这里的模板：

<?xml version="1.0" ?>
<Application Name="CONTROLLER_A" Type="Application">
  <InstanceHelp> This is a template for creating a controller applications.</InstanceHelp>
  <Handle>12</Handle>
  <!-- NetworkInterfaces -->
   <NetworkInterface Name="ETH0" MAC="" IPAddress="192.168.0.21" SubnetMask="255.255.255.0"></NetworkInterface>
  <CNTMessages>
  </CNTMessages>
  <!-- Console configuration
  Mode: VGA, VESA_8, VESA_16, VESA_24, VESA_32, Text, None. Use VESA_32 for graphical displays, Text or None for alphanumeric displays.
  Background: Black, White, LightGray, DarkGray, Brown
  Resolution: 320x240, 640x480, 800x600, 1024x768, 1280x1024
  HFreqMin: Minimum horizontal frequency [KHz], limited by monitor.
  HFreqMax: Maximum horizontal frequency [KHz], limited by monitor.
  VFreqMin: Minimum vertical frequency [Hz], limited by monitor.
  HFreqMax: Maximum vertical frequency [Hz], limited by monitor.
  VerticalRefresh: Desired vertical refresh rate [Hz], actual refresh may deviate some from specified value.
  MouseSpeed: Mouse speed multiplier.
  -->
  <Console Mode="VESA_32" Background="#f0f0f0" Resolution="1024x768" HFreqMin="24.0" HFreqMax="62.0" VFreqMin="50.0" VFreqMax="77.0" VerticalRefresh="75" Mouse="PS2" MouseSpeed="1"></Console>

  <Components>
  </Components>

  <!-- Custom application values -->

  <Subcomponents>
  <!-- I/o servers, -->
    <Subcomponent Name="Main" Type="Entry" src="Components\CNT\Main.xml"></Subcomponent>
    <Subcomponent Name="Messenger" Type="Messenger" src="Components\CNT\Messenger.xml"></Subcomponent>
    <Subcomponent Name="WebServer" Type="WebServer" src="Components\CNT\WebServer.xml"></Subcomponent>
    <Subcomponent Name="SerialStringCom1" Type="SerialStringDispatcher" src="Components\SerialStringCom1.xml"></Subcomponent>
    <Subcomponent Name="SerialStringCom2" Type="SerialStringDispatcher" src="Components\SerialStringCom2.xml"></Subcomponent>
    <Subcomponent Name="SerialStringCom3" Type="SerialStringDispatcher" src="Components\SerialStringCom3.xml"></Subcomponent>
    <Subcomponent Name="NmeaLog"   Type="SimpleNMEA" src="Components\NmeaLog.xml"></Subcomponent>
    <Subcomponent Name="NmeaAlarm" Type="SimpleNMEA" src="Components\NmeaAlarm.xml"></Subcomponent>
    <Subcomponent Name="NmeaCon"   Type="SimpleNMEA" src="Components\NmeaCon.xml"></Subcomponent>

    <Subcomponent Name="CANIO"        Type="CANIO"         src="Components/CANIO.xml"></Subcomponent>
    <Subcomponent Name="MessengerIOServer" Type="MessengerIOServer" src="Components\CNT\MessengerIOServer.xml"></Subcomponent>
    <Subcomponent Name="CodeSys" Type="CoDeSys" src="Components\CodeSys.xml"></Subcomponent>

  </Subcomponents>

  <Signals>
    <!-- Sysinfo signals -->
    <Signal Name="CPULoad"          Value="1.0" Type="double" RouteType="Variable" Routing="No routing" Description="CPU load [busy/idle, 0..1]."></Signal>
    <Signal Name="MemTotal"         Value="0"   Type="double" RouteType="Variable" Routing="No routing" Description="Memory total [kb]."></Signal>
    <Signal Name="MemUsed"          Value="0"   Type="double" RouteType="Variable" Routing="No routing" Description="Memory used [kb]."></Signal>
    <Signal Name="MemUsedRelative"  Value="0"   Type="double" RouteType="Variable" Routing="No routing" Description="Memory used relative [used/total, 0..1]."></Signal>
  </Signals>
  <Alarms>
    <Alarm Name="Broken signal routing" Level="Error" Enabled="1" Text="Something is wrong with the routing for one or more of the signals." Description="Something is wrong with the routing for one or more of the signals."></Alarm>
    <Alarm Name="Signal not updated"      Level="Error" Enabled="1" Text="The signal is not updated (time stamp too old). Probably lost connection with i/o."   Description="The signal is not updated (time stamp too old). Probably lost connection with i/o.."></Alarm>
  </Alarms>
</Application>

特别是：说我要删除：

<Subcomponent Name="NmeaCon" Type="SimpleNMEA" src="Components\NmeaCon.xml"></Subcomponent>

还要改变：

<Subcomponent Name="CANIO" Type="CANIO" src="Components/CANIO.xml"></Subcomponent>

进入：

<Subcomponent Name="CANOPEN" Type="CANOPEN" src="Components/Canopen/CANOPEN.xml"></Subcomponent>

编辑

对于删除我已经尝试过：

Set appXML = New MSXML2.DOMDocument
With appXML
    If Not .LoadXML("ControllerApp.xml") Then Exit Sub
    Set xmlSubTree = .SelectSingleNode("/Application/Subcomponents/Subcomponent").ParentNode
    dumString =  "//Subcomponent[@Name='SerialStringCom1']"
    Set oldNode = xmlSubTree.SelectSingleNode(dumString)
    ' checking oldNode here gives all the expected attributes values (text)
    ' but it fails on the next statement: Object does not support this property or method:
    oldNode.ParentNode.RemoveChild(oldNode)
End With

编辑2：对，我以为我很笨，但看起来我太乐观了，自我假设：我远比那笨。该死的 VBA 不能返回 void 的东西（不是每次我必须回到 Office mumbo jumbo 时我都偶然发现这个）所以：

' you got to catch the returned object :o you dumb fool
Set zombie = oldNode.ParentNode.RemoveChild(oldNode)

奇迹般有效。我向模组道歉......

标签： xmlvba

解决方案

使用 XSLT 很容易实现。您只需要从 VBA 调用 XSLT。

XSLT 将处理您的输入 XML 并生成一个新的 XML 文件：

XML + XSLT => 新 XML

XSLT

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

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

    <!-- remove Subcomponent template -->
    <xsl:template match='Subcomponent[@Name="NmeaCon" and @Type="SimpleNMEA" and @src="Components\NmeaCon.xml"]'>
    </xsl:template>

    <!-- modify Subcomponent template -->
    <xsl:template match='Subcomponent[@Name="CANIO" and @Type="CANIO" and src="Components/CANIO.xml"]'>
        <Subcomponent Name="CANOPEN" Type="CANOPEN" src="Components/Canopen/CANOPEN.xml"></Subcomponent>
    </xsl:template>
</xsl:stylesheet>

VBA

Private Sub Transform(sourceFile, stylesheetFile, resultFile)

    Dim source As New MSXML2.DOMDocument60
    Dim stylesheet As New MSXML2.DOMDocument60
    Dim result As New MSXML2.DOMDocument60

    ' Load data.
    source.async = False
    source.Load sourceFile

    ' Load style sheet.
    stylesheet.async = False
    stylesheet.Load stylesheetFile

    If (source.parseError.ErrorCode <> 0) Then
       MsgBox ("Error loading source document: " & source.parseError.reason)
    Else
        If (stylesheet.parseError.ErrorCode <> 0) Then
            MsgBox ("Error loading stylesheet document: " & stylesheet.parseError.reason)
        Else
            ' Do the transform.
            source.transformNodeToObject stylesheet, result
            result.Save resultFile
        End If
    End If

End Sub

推荐阅读