excel 来自VBA的SOAP请求

j0pj023g 于 2023-03-13 发布在其他

关注(0)|答案(1)|浏览(136)

我想使用VBA Excel发出SOAP请求VBA请求

Public Sub httpclient()
        Dim Req As Object
        Dim sEnv As String
        Dim Resp As New MSXML2.DOMDocument60
        Set Req = CreateObject("MSXML2.XMLHTTP")
        Set Resp = CreateObject("MSXML2.DOMDocument.6.0")
        Req.Open "GET", "https://api.etadirect.com/soap/capacity/?wsdl", False
        sEnv = sEnv & "<soapenv:Envelope xmlns:soapenv=""http://schemas.xmlsoap.org/soap/envelope"">"
         sEnv = sEnv & "  <soapenv:Header/>"
         sEnv = sEnv & "  <soapenv:Body>"
         sEnv = sEnv & "   <Request>"
         sEnv = sEnv & "    <now>2019-07-22</now>"
         sEnv = sEnv & "    <login>string</login>"
         sEnv = sEnv & "    <company>string</company>"
         sEnv = sEnv & "    <auth_string>string</auth_string>"
         sEnv = sEnv & "   </Request>"
         sEnv = sEnv & "  </soapenv:Body>"
         sEnv = sEnv & "</soapenv:Envelope>"
    ' Send SOAP Request
        Req.Send (sEnv)
        Debug.Print Req.responseText
    End Sub

SOAP UI请求

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:urn="urn:toa:capacity">
       <soapenv:Header/>
       <soapenv:Body>
          <urn:get_quota_data>
             <user>
                <now>2019-07-22</now>
                <login>string</login>
                <company>string</company>
                <auth_string>string</auth_string>
             </user>
          </urn:get_quota_data>
       </soapenv:Body>
    </soapenv:Envelope>

我期望获得下面的结果，而不是我获得了大量的xml标记。

<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/">
       <SOAP-ENV:Body>
          <SOAP-ENV:Fault>
             <faultcode>SOAP-ENV:Client</faultcode>
             <faultstring>Bad request format - Mandatory parameter is absent: 'date'</faultstring>
             <detail>
                <errorCode>100</errorCode>
             </detail>
          </SOAP-ENV:Fault>
       </SOAP-ENV:Body>
    </SOAP-ENV:Envelope>

SOAPUI返回强制参数是缺席的，因为我写的请求只是为了验证。在相同的vba中，我没有收到此消息。问题是我在VBA代码中做错了什么。你能帮助我吗？最好的问候，

excel

来源：https://stackoverflow.com/questions/57146366/soap-request-from-vba

1条答案

按热度按时间

zfycwa2u1#

我遇到的主要问题是SOAP标记soapenv（泛型）和sear（特定于服务）的正确声明。这个VBA函数可以获取任意数量的节点，尽管在我的例子中，我只对Web服务中数千个可用对象中的一个感兴趣。

Public Function GetWebServiceData() As String

出错时后藤ErrHandler将xmlhttp变小为新MSXML2.XMLHTTP60将URL变小为字符串变小为字符串变小为字符串变小为字符串结果

Dim xmlDoc As MSXML2.DOMDocument60
Dim xmlNodeList As MSXML2.IXMLDOMNodeList
Dim xmlNode As MSXML2.IXMLDOMNode

' Construct the URL to call the web service with the parameters

 URL = "http://[redacted url to one endpoint]"

qry = "<soapenv:Envelope xmlns:soapenv=""http://schemas.xmlsoap.org/soap/envelope/"" >" & _
"<sear:searchEquipment xmlns:sear=""[http://[redacted]]/]"">" & _
           "<soapenv:Header/><soapenv:Body>"
qry = qry & "<sear:searchEquipment>"
qry = qry & "<sear:queryDate>2023-03-09T00:00:00Z</sear:queryDate>"
qry = qry & "<sear:searchType>Equipment</sear:searchType>"
qry = qry & "<sear:searchBy>EQUIP</sear:searchBy>"
qry = qry & "<sear:searchValue>25DG-105</sear:searchValue>"
qry = qry & "</sear:searchEquipment>"
qry = qry & "</soapenv:Body>" & _
           "</soapenv:Envelope>"

' Open the URL and get the response
xmlhttp.Open "POST", URL, False 
xmlhttp.SetRequestHeader "Content-Type", "text/xml"
'Debug.Print qry
xmlhttp.Send qry
result = xmlhttp.ResponseText

' Load the XML document from a string
Set xmlDoc = New MSXML2.DOMDocument60
xmlDoc.LoadXML result

' Extract the values from the XML document
Set xmlNodeList = xmlDoc.getElementsByTagName("[redacted]")(0).ChildNodes

For Each xmlNode In xmlNodeList
    Select Case xmlNode.nodeName
        Case "owner"
            
        Case "assignedLocation"
        Case "currLoc"
        'Case "id"
        Case "plate"
        Case "vin"
        Case Else
            ' ignore
    End Select
    Debug.Print xmlNode.nodeName, xmlNode.Text
Next xmlNode

GoTo CleanUp

ErrHandler：result = Err.说明恢复清理恢复'仅调试清理：'返回结果GetWebServiceData =结果结束函数

赞(0）回复(0）举报 2023-03-13

我来回答

excel 来自VBA的SOAP请求

1条答案

相关问题

热门标签

最新问答