使用VBA的getElementsByTagName查找XML标签返回长度为0的问题

尝试用VBA编辑XML文件，调用getElementsByTagName方法查找标签，但始终返回空结果，其他标签的查询也无法获取到内容。

XML文件内容 #

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui" onLoad="OnLoad">
    <ribbon>
        <tabs>
            <tab label="Lab" id="ID1" insertAfterMso="TabHome">
                <group id="Grp1" label="Group 1">
                    <button id="Button" imageMso="FunctionsInformationInsertGallery" size="large" label="Button"/>
                </group>
                <group id="Grp2" label="Group 2">
                </group>
            </tab>
        </tabs>
    </ribbon>
</customUI>

VBA代码 #

Sub ModXML()
Dim rXML As String
Dim respXML As New MSXML2.DOMDocument60
Dim lst As MSXML2.IXMLDOMNodeList

rXML = ThisWorkbook.path & "\ex\example.xml"
Set respXML = New DOMDocument60
respXML.Load (rXML)

Set lst = respXML.getElementsByTagName("tab")
Debug.Print respXML.XML
Debug.Print lst.Length

End Sub

调试输出 #

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui" onLoad="OnLoad">
    <ribbon>
        <tabs>
            <tab label="Lab" id="ID1" insertAfterMso="TabHome">
                <group id="Grp1" label="Group 1">
                    <button id="Button" imageMso="FunctionsInformationInsertGallery" size="large" label="Button"/>
                </group>
                <group id="Grp2" label="Group 2">
                </group>
            </tab>
        </tabs>
    </ribbon>
</customUI>

 0 

解决方法 #

问题根源在于XML的根节点customUI带有命名空间xmlns="http://schemas.microsoft.com/office/2009/07/customui"，而getElementsByTagName方法默认不会识别命名空间，因此无法匹配到属于该命名空间下的tab等标签。

方法1：使用命名空间管理器查询

通过设置SelectionNamespaces属性指定命名空间，再用selectNodes方法配合命名空间前缀查询：

Sub ModXML()
Dim rXML As String
Dim respXML As New MSXML2.DOMDocument60
Dim lst As MSXML2.IXMLDOMNodeList

rXML = ThisWorkbook.Path & "\ex\example.xml"
Set respXML = New DOMDocument60
' 注册命名空间，前缀cu可自定义
respXML.SetProperty "SelectionNamespaces", "xmlns:cu=""http://schemas.microsoft.com/office/2009/07/customui"""
respXML.Load (rXML)

' 使用前缀查询目标标签
Set lst = respXML.SelectNodes("//cu:tab")
Debug.Print lst.Length ' 输出1，成功获取到节点
End Sub

方法2：忽略命名空间，通过本地名称匹配

遍历所有节点，判断节点的LocalName是否为目标标签名：

Sub ModXML()
Dim rXML As String
Dim respXML As New MSXML2.DOMDocument60
Dim node As MSXML2.IXMLDOMNode
Dim resultNodes As New Collection

rXML = ThisWorkbook.Path & "\ex\example.xml"
Set respXML = New DOMDocument60
respXML.Load (rXML)

' 遍历所有节点，收集本地名称为tab的节点
For Each node In respXML.SelectNodes("//*")
    If node.LocalName = "tab" Then
        resultNodes.Add node
    End If
Next node

Debug.Print resultNodes.Count ' 输出1，成功获取到节点
End Sub

内容的提问来源于stack exchange，提问作者Munki Fisht