如何通过Excel VBA仅提取XML中<InvoiceHeader>标签下的目标数据？

修改后的VBA代码 #

Sub cmdImportDataFromFile()
    Dim fd As Office.FileDialog
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    
    With fd
        If .Show = True Then
            Dim xmlfilename As String
            xmlfilename = .SelectedItems(1)
            
            ' 加载XML文档
            Dim xDoc As Object
            Set xDoc = CreateObject("MSXML2.DOMDocument")
            xDoc.async = False
            xDoc.validateOnParse = False
            xDoc.Load xmlfilename
            
            ' 定位到InvoiceHeader节点
            Dim invoiceHeaderNode As Object
            Set invoiceHeaderNode = xDoc.SelectSingleNode("//InvoiceHeader")
            
            If Not invoiceHeaderNode Is Nothing Then
                ' 获取指定标签的文本值
                Dim poValue As String, invNumValue As String, currValue As String
                poValue = invoiceHeaderNode.SelectSingleNode("PurchaseOrder").Text
                invNumValue = invoiceHeaderNode.SelectSingleNode("InvoiceNumber").Text
                currValue = invoiceHeaderNode.SelectSingleNode("BuyerCurrency").Text
                
                ' 将数据写入活动单元格下方的对应行
                With ActiveCell
                    .Offset(1, 0).Value = poValue ' 活动单元格下1行放PurchaseOrder
                    .Offset(2, 0).Value = invNumValue ' 活动单元格下2行放InvoiceNumber
                    .Offset(3, 0).Value = currValue ' 活动单元格下3行放BuyerCurrency
                End With
            Else
                MsgBox "未找到InvoiceHeader节点"
            End If
        End If
    End With
End Sub

关键修改说明 #

• 精准定位节点：用SelectSingleNode("//InvoiceHeader")直接锁定目标节点，避免依赖子节点顺序（原代码用索引定位易因XML结构变动出错）；再通过节点内的SelectSingleNode("标签名")提取对应字段，逻辑更可靠。
• 按规则写入单元格：借助ActiveCell.Offset(n,0)控制写入位置，Offset(1,0)对应活动单元格下方第1行，完全匹配你要求的布局。
• 基础错误处理：增加节点存在性判断，避免因XML结构异常导致运行报错。

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