RogueSolutions
asked on
Create a SOAP Request/Response using XML from VBA
I am trying to create a SOAP call from within Excel VBA using XML. It the final solution I don't know much about the machine that will run the code so I can't expect or install the SOAP toolkit or anything like that. I have to use very basic elements. I am making the assumption that XML will be installed but will use late binding to 'try' for different versions.
Based on some research and the client's "development" document that details the ExportData function I have got the code below.
The commented out XMLDOM code is not needed as yet (I think) but will be used to process the result.
Currently if I run it I get nonsense back - a stream of question marks with the odd dot or numeric in it.
If I run the SOAP request in a test software like SoapUI then I get an "Invalid Path" error but thats due to my export being configured wrongly I think - possibly the test/testExpo table isn't present on my test web service. I'm working on that aspect with the client though if anything obvious is wrong please let me know!
I want, for now, to get the same error back via XML that I get from SoapUI as I know that is the server responding at least.
Thanks
Simon
---
Dim oXML As Object
Dim oDom As Object
Dim sReq As String
sReq = ""
sReq = sReq & "<soapenv:Envelope " & vbCrLf
sReq = sReq & " xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" " & vbCrLf
sReq = sReq & " xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" " & vbCrLf
sReq = sReq & " xmlns:soapenv=""http://schemas.xmlsoap.org/soap/envelope/"" " & vbCrLf
sReq = sReq & " xmlns:enab=""enablon"">" & vbCrLf
sReq = sReq & " <soapenv:Header>" & vbCrLf
sReq = sReq & " <tns:AuthHeader id=""h_id1"">" & vbCrLf
sReq = sReq & " <EnaUserName xsi:type=""xsd:string"">na
sReq = sReq & " <EnaPassword xsi:type=""xsd:string"">pa
sReq = sReq & " </tns:AuthHeader>" & vbCrLf
sReq = sReq & " </soapenv:Header>" & vbCrLf
sReq = sReq & " <soapenv:Body>" & vbCrLf
sReq = sReq & " <enab:ExportData soapenv:encodingStyle=""http://schemas.xmlsoap.org/soap/encoding/""
sReq = sReq & " <Table xsi:type=""xsd:string"">/t
sReq = sReq & " <FolderId xsi:type=""xsd:int"">0</Fo
sReq = sReq & " <Fields xsi:type=""xsd:string"">f1
sReq = sReq & " <Filter xsi:type=""xsd:string""></
sReq = sReq & " <CSVSeparator xsi:type=""xsd:int"">1</CS
sReq = sReq & " <FormatOptions xsi:type=""xsd:int"">22</F
sReq = sReq & " <FormatType xsi:type=""xsd:int"">2</Fo
sReq = sReq & " <ExportOptions xsi:type=""xsd:int"">1</Ex
sReq = sReq & " </enab:ExportData>" & vbCrLf
sReq = sReq & " </soapenv:Body>" & vbCrLf
sReq = sReq & "</soapenv:Envelope>" & vbCrLf
Set oXML = CreateObject("MSXML2.Serve
'Set oDom = CreateObject("Microsoft.XM
oXML.Open "POST", "https://rct2.enablon.com/6.0-6/sedex/rpc_encoded.wsdl", False
oXML.setRequestHeader "Content-Type", "text/xml"
'oDom.loadXML sReq
oXML.sEnd sReq
Debug.Print oXML.responseBody
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yep, thanks to both of you for your thoughts.
I am working on speaking to the web service supplier (third party rather than my actual client)
I am working on speaking to the web service supplier (third party rather than my actual client)
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
ASKER
Thanks for that, have added the loop and I now get proper text out.
Unfortunately its an HTML page indicating "The page you are looking for cannot be displayed because an invalid method (HTTP verb) was used to attempt access."
I suspect that means my WSDL location is incorrect? The address used in the VBA is the same as I gave SoapUI and that worked ok, the web service responded at least. Do I need to change how I address the WSDL?