如何用Excel VBA移除XML文件中的未知特殊字符？

我正在开发一款VBA脚本，用于读取XML文件中的值并解析至Excel表格。这些XML文件由计算软件生成，包含未知特殊字符（�），示例XML内容如下：

<be05key_fig version="10.19.7.22" date="21.11.2022 10.31" company="SBi" model="TEST_BE">
<table head="Be18 n�gletal: Munkes�huse bygning E"></table>
<table head="Transmissionstab, W/m�">
    <row><id>Transmissionstabsramme, almindelig</id><value>14,3</value></row>
    <row><id>Transmissionstabsramme, lavenergi</id><value>13,3</value></row>
    <row><id>Transmissionstab, beregnet</id><value>10,7</value></row>
</table>
<table head="Antal timer temperatur over">
    <row><id>26 �C (supplerende oplysning)</id><value>0</value></row>
    <row><id>27 �C</id><value>0</value></row>
    <row><id>28 �C</id><value>0</value></row>
    <row><id>29 �C (supplerende oplysning)</id><value>0</value></row>
</table>
</be05key_fig>

期望处理后，特殊字符被移除，得到如下内容：

<be05key_fig version="10.19.7.22" date="21.11.2022 10.31" company="SBi" model="TEST_BE">
<table head="Be18 ngletal: Munkeshuse bygning E"></table>
<table head="Transmissionstab, W/m">
    <row><id>Transmissionstabsramme, almindelig</id><value>14,3</value></row>
    <row><id>Transmissionstabsramme, lavenergi</id><value>13,3</value></row>
    <row><id>Transmissionstab, beregnet</id><value>10,7</value></row>
</table>
<table head="Antal timer temperatur over">
    <row><id>26 C (supplerende oplysning)</id><value>0</value></row>
    <row><id>27 C</id><value>0</value></row>
    <row><id>28 C</id><value>0</value></row>
    <row><id>29 C (supplerende oplysning)</id><value>0</value></row>
</table>
</be05key_fig>

我当前使用以下VBA代码访问XML：

Sub Test()
     
        Dim xmlObj As Object
        Set xmlObj = CreateObject("MSXML2.DOMDocument")
        
        xmlObj.SetProperty "SelectionLanguage", "XPath"
        xmlObj.async = False
        xmlObj.validateOnParse = False
        xmlObj.Load ("C:\BE\Nøgletalsfil.xml")
        
        Debug.Print xmlObj.SelectNodes("//table[@head = 'Transmissionstab, W/m']/row/value")(0).Text
        
    End Sub

请问能否通过Excel VBA直接移除这些特殊字符，实现上述处理效果？

可以通过VBA先读取XML文件的原始文本，清理特殊字符后再加载到DOMDocument中，具体实现如下：

Sub CleanXMLAndParse()
    Dim fso As Object
    Dim xmlText As String
    Dim cleanedXmlText As String
    Dim xmlObj As Object
    Dim regex As Object
    
    ' 初始化文件系统对象
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    ' 读取XML文件内容（按Unicode格式读取避免编码错乱）
    xmlText = fso.OpenTextFile("C:\BE\Nøgletalsfil.xml", 1, False, -2).ReadAll
    
    ' 初始化正则表达式，匹配无法识别的特殊字符
    Set regex = CreateObject("VBScript.RegExp")
    regex.Global = True
    regex.Pattern = "[^\x00-\x7F]" ' 匹配所有非ASCII字符，可按需调整
    
    ' 移除特殊字符
    cleanedXmlText = regex.Replace(xmlText, "")
    
    ' 可选：针对特定场景做精准替换（比如还原丹麦语字符）
    cleanedXmlText = Replace(cleanedXmlText, "ngletal", "nøgletal")
    cleanedXmlText = Replace(cleanedXmlText, "Munkeshuse", "Munkeshuse")
    cleanedXmlText = Replace(cleanedXmlText, "�C", "C")
    
    ' 加载清理后的XML内容
    Set xmlObj = CreateObject("MSXML2.DOMDocument")
    xmlObj.SetProperty "SelectionLanguage", "XPath"
    xmlObj.async = False
    xmlObj.validateOnParse = False
    xmlObj.LoadXML cleanedXmlText ' 加载处理后的文本而非原文件
    
    ' 测试解析结果
    If xmlObj.SelectNodes("//table[@head = 'Transmissionstab, W/m']/row/value").Length > 0 Then
        Debug.Print xmlObj.SelectNodes("//table[@head = 'Transmissionstab, W/m']/row/value")(0).Text
    Else
        Debug.Print "未找到目标节点"
    End If
    
    ' 释放对象
    Set fso = Nothing
    Set regex = Nothing
    Set xmlObj = Nothing
End Sub

关键说明 #

1. 文件读取：使用Scripting.FileSystemObject按Unicode格式读取文件，避免编码问题导致的字符错乱。
2. 字符清理：正则表达式[^\x00-\x7F]匹配所有非ASCII字符，若需保留丹麦语的æøå等字符，可调整正则为[^\x00-\x7F\xC0-\xFF]缩小匹配范围。
3. 精准替换：如果明确知道特殊字符对应的正确内容，可添加Replace语句做精准还原，提升处理准确性。
4. XML加载：使用LoadXML方法加载清理后的文本，确保后续解析的是处理后的内容。

内容的提问来源于stack exchange，提问作者Frederik Seeberg