调用API后在Excel中解析XML运单状态的VBA问题求助

Let's fix your VBA code step by step. I've gone through your existing code and the XML structure you provided, and here's what's going wrong and how to correct it:

现有代码的关键问题 #

• 变量名不匹配：你用xmlHTTP发起请求，但后面写了strReap = hReq.ResponseText，hReq根本没定义，应该改成xmlHTTP.ResponseText
• 错误的节点定位逻辑：你的XML里<Shipment>是一个子标签，不是其他节点的属性，但原代码试图从子节点的属性里获取Shipment，这完全不符合你的XML结构
• 未指定目标工作表：代码里用了ws但没定义，Excel不知道要输出到哪个工作表
• 列变量赋值错误：你先把strCol1设为"A"，又立刻改成"B"，而且代码里根本没用到这两个变量

修正后的完整代码 #

这个代码会准确定位到<Shipment>标签，然后把该标签及其所有子节点的XML内容输出到Excel的指定工作表中：

Sub ExtractShipmentXML()
    Dim xmlHTTP As Object
    Dim xmlDoc As New MSXML2.DOMDocument
    Dim shipmentNode As MSXML2.IXMLDOMNode
    Dim ws As Worksheet
    Dim myURL As String
    Dim strResp As String
    Dim intRow As Integer
    
    ' 指定要输出的工作表，可根据你的需求修改
    Set ws = ThisWorkbook.Sheets("Sheet1")
    intRow = 2 ' 从第2行开始输出
    
    ' 初始化HTTP请求对象
    Set xmlHTTP = CreateObject("MSXML2.ServerXMLHTTP.6.0")
    myURL = "http://xxxxxxxxxxxxx:15555/gateway/StatusTracking/1.0/shipment/tracking?housebill=cvvvv"
    
    ' 发送API请求
    xmlHTTP.Open "GET", myURL, False
    xmlHTTP.SetRequestHeader "APIKey", "xxxx-xxx-xxxxx-xxxx-xxxx"
    xmlHTTP.SetRequestHeader "Accept", "application/xml" ' 修正为XML格式匹配请求头
    xmlHTTP.Send
    
    ' 获取响应文本
    strResp = xmlHTTP.ResponseText
    
    ' 加载XML文档并检查错误
    xmlDoc.async = False
    xmlDoc.validateOnParse = False
    If Not xmlDoc.LoadXML(strResp) Then
        MsgBox "XML加载错误: " & xmlDoc.parseError.reason
        GoTo Cleanup
    End If
    
    ' 定位到<Shipment>标签
    Set shipmentNode = xmlDoc.getElementsByTagName("Shipment").Item(0)
    
    ' 检查是否找到该节点
    If shipmentNode Is Nothing Then
        MsgBox "未找到<Shipment>标签"
        GoTo Cleanup
    End If
    
    ' 将<Shipment>标签及其子节点的XML内容输出到Excel
    ws.Cells(intRow, 1).Value = shipmentNode.XML
    
Cleanup:
    ' 释放对象
    Set xmlHTTP = Nothing
    Set xmlDoc = Nothing
    Set shipmentNode = Nothing
    Set ws = Nothing
End Sub

关键说明 #

• API请求的Accept头：你当前设置的是Accept: application/json，但API返回的是XML，这里改成application/xml，避免格式不匹配的问题
• XML节点定位：用getElementsByTagName("Shipment").Item(0)直接获取第一个<Shipment>节点，然后用shipmentNode.XML获取该节点的完整XML文本
• 错误处理：添加了XML加载错误提示和节点未找到的提示，方便调试
• 工作表指定：明确指定了输出的工作表，你可以把"Sheet1"改成你实际要用的工作表名称

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