VBA发送XML至URL时Mid函数调用报错请求协助修复

Let's break down why you're hitting this error and how to fix it properly.

Root Cause of the Error #

The "Invalid Procedure call" error on your Mid line is almost certainly because one or both of your InStr calls returned 0 (meaning they couldn't find the target substring <ReferenceTransmissionNo> or </ReferenceTransmissionNo> in the response). When either Strpos1 or Strpos2 is 0, calculating (Strpos2 - (Strpos1 + Strposlen)) gives a negative number or zero, which is invalid input for the Mid function.

This can happen for a few reasons:

• The OTM system's XML response doesn't include the ReferenceTransmissionNo tag at all
• The tag uses different casing (e.g., referencetransmissionno instead of camel-case)
• The response uses unescaped </> instead of the </> you're searching for
• There's extra whitespace or formatting in the tag that breaks your string match

Better Fix: Use XML DOM Parsing (Instead of String Slicing) #

String-based XML parsing is fragile—small changes in the response format break your code. You already have a DOMDocument60 object declared; let's use it properly to safely extract the value you need.

Here's the revised code section to replace your string-slicing logic:

' Load the response into the DOM document (validate it first)
If oXmlReturn.LoadXML(oXmlHttp.responseText) Then
    Set xmlroot = oXmlReturn.DocumentElement
    
    ' Use XPath to locate the ReferenceTransmissionNo node (adjust XPath if needed)
    Dim refTransNode As IXMLDOMNode
    Set refTransNode = xmlroot.SelectSingleNode("//ReferenceTransmissionNo")
    
    If Not refTransNode Is Nothing Then
        ' Successfully found the node: write its text value to the cell
        Dt.Range("AO" & x).Value = refTransNode.Text
    Else
        ' Handle the case where the node is missing to avoid errors
        Dt.Range("AO" & x).Value = "RefTransNo Not Found"
    End If
Else
    ' Handle invalid XML response from OTM
    Dt.Range("AO" & x).Value = "Invalid XML Response"
End If

x = x + 1 ' Adjust increment as needed (I assume `x = x + i` was a typo)

Additional Improvements to Prevent Future Errors #

1. Check HTTP Request Status
   Before processing the response, verify the request succeeded. Add this right after the .send call:

   With oXmlHttp
       .send xmlInput
       ' Validate the request returned a successful HTTP status code
       If .Status <> 200 Then
           MsgBox "OTM Request Failed: Status " & .Status & " - " & .StatusText
           GoTo Cleanup
       End If
   End With
   

2. Add Proper Error Handling
   Wrap your code in error handling to catch unexpected issues and clean up resources:

   On Error GoTo ErrHandler
   ' ... your existing code ...
   
   

Cleanup:
' Release objects to avoid memory leaks
Set oXmlHttp = Nothing
Set oXmlReturn = Nothing
Set xmlroot = Nothing
Set refTransNode = Nothing
Exit Sub

ErrHandler:
MsgBox "Error occurred: " & Err.Description & " (Error #" & Err.Number & ")"
Resume Cleanup

3. **Debug the Raw Response**
If you're still not finding the node, print the full response text to see exactly what you're receiving:
```vba
Debug.Print oXmlHttp.responseText ' Check this in the VBA Immediate Window

This will reveal if the tag is named differently, uses unescaped characters, or is missing entirely.

Why This Works #

Using the DOM parser lets you interact with the XML as a structured document, not just a string. XPath queries (//ReferenceTransmissionNo) are flexible and handle minor formatting changes (like whitespace) that break string matches. Plus, we explicitly check if the node exists before trying to read its value, eliminating the invalid Mid call entirely.

内容的提问来源于stack exchange，提问作者Arvind kumar T