如何使用VBA提取深层嵌套XML文件指定信息并导出至Excel

实现逻辑 #

• 关闭XML异步加载，确保文件加载完成后再执行后续操作，新增加载失败校验避免运行报错
• 提取第一类目标信息：根节点<instrument>下name属性为name的<string>节点的value值，写入Sheet1的A1单元格作为列标题
• 提取第二类目标信息：所有<obj class="PSoundSlot">节点下name成员内的<string name="s">节点的value值，依次写入A列标题下方的行

完整可运行代码 #

Sub ReadXML()
    Call fnReadXMLByTags
End Sub

Function fnReadXMLByTags()
    Dim mainWorkBook As Workbook
    Dim oXMLFile As Object
    Dim headerNode As Object
    Dim slotNodes As Object
    Dim i As Integer
    
    Set mainWorkBook = ActiveWorkbook
    mainWorkBook.Sheets("Sheet1").Range("A:A").Clear
    
    ' 初始化XML对象
    Set oXMLFile = CreateObject("Microsoft.XMLDOM")
    oXMLFile.async = False ' 关闭异步加载，确保文件加载完成再操作
    XMLFileName = "C:\Users\xxx\Documents\TestFile.xml" ' 替换为你的XML文件实际路径
    
    ' 加载XML并校验
    If Not oXMLFile.Load(XMLFileName) Then
        MsgBox "XML文件加载失败，请检查路径是否正确、文件格式是否合法"
        Exit Function
    End If
    
    ' 提取第一类信息：作为列标题
    Set headerNode = oXMLFile.SelectSingleNode("/instrument/string[@name='name']")
    If Not headerNode Is Nothing Then
        mainWorkBook.Sheets("Sheet1").Range("A1") = headerNode.getAttribute("value")
    End If
    
    ' 提取第二类信息：所有slot的name值
    Set slotNodes = oXMLFile.SelectNodes("//member[@name='slots']//obj[@class='PSoundSlot']//member[@name='name']/string[@name='s']")
    For i = 0 To slotNodes.Length - 1
        mainWorkBook.Sheets("Sheet1").Range("A" & i + 2) = slotNodes(i).getAttribute("value")
    Next i
End Function

代码说明 #

• XPath路径/instrument/string[@name='name']直接定位你标注的第一类待提取节点，getAttribute("value")可以读取节点对应属性的值
• XPath路径//member[@name='slots']//obj[@class='PSoundSlot']//member[@name='name']/string[@name='s']使用双斜杠忽略中间嵌套层级，可匹配所有你标注的第二类待提取节点，即使后续XML嵌套结构变化，只要节点属性不变匹配逻辑依然生效

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