I'm trying to send a request to a web service for our accounting software using VBA in Excel. The HTTP request is new to me, but the VBA part is not. Any help would be greatly appreciated, especially explanations on how this process works. I'd like to understand it, but so far everything I have found has not worked and has just made me more confused.

I've done some research and tried to figure this out myself, and I've made some progress. Now I'm just hitting a wall with how and/or where to send the request.

The technical documentation gives me method names and WSDL locations, and I've gotten the WSDL for the GET method I'm trying to use. I used SOAPUI to build a SOAP Envelope based on the WSDL.

My (censored) VBA code is below:

Quote:

---

Sub GetJobTest()

Dim strURL As String
Dim strWSDLURL As String
Dim strEnv As String
Dim objHTTP As New MSXML2.XMLHTTP60
Dim objXMLDoc As New MSXML2.DOMDocument60
Dim xmlReturnArray As MSXML2.IXMLDOMNode
Dim xmlReturnElement As MSXML2.IXMLDOMNode

guid = CreateGuidString() 'Creates a GUID using a function that isn't shown
strURL = "https://ourserver.com:####/ws/GetJob"
strWSDLURL = "https://ourserver.com:####/ws/GetJob"
strResult = ""

'Build SOAP envelope
strEnv = "<soapenv:Envelope xmlns:soapenv=""http://schemas.xmlsoap.org/soap/envelope/"" xmlns:typ=""http://www.northgate-is.com/proiv/webservices/types"">" _
& "<soapenv:Header/>" _
& "<soapenv:Body>" _
& "<typ:GetJob>" _
'Stuff goes here'
& "</typ:GetJob>" _
& "</soapenv:Body>" _
& "</soapenv:Envelope>" _

'Debug.Print strEnv

With objHTTP
.Open "GET", strWSDLURL, False 'Open HTTP object connection
.setRequestHeader "Content-Type", "text/xml;charset=UTF-8" 'Set request header content type
.setRequestHeader "soapAction", strURL 'Set SOAP action of request header
.setRequestHeader "Accept-encoding", "gzip,deflate" 'Set request header encoding
.setRequestHeader "Connection", "Keep-Alive" 'This does something
.send strEnv 'Send Request with SOAP envelope
End With

objXMLDoc.LoadXML (objHTTP.responseText)

Debug.Print objHTTP.responseText 'print the response

Set objHTTP = Nothing

Set objXMLDoc = Nothing

End Sub

---

Whenever I run this, all I get back is html that produces a table with Endpoint and WSDL links for the method. There's no xml SOAP response, which is what I'm expecting back. Am I sending this to the wrong URL or something? Is my request header not right?

A GET request doesn't have a body. Everything is defined as query string params in the url. So I'm wondering what the actual url you are sending to the server looks like. Try changing the method to POST and see what happens.

I tried POST and got this:

Quote:

---

<SOAP-ENV:Envelope xmlns:SOAP-ENV='http://schemas.xmlsoap.org/soap/envelope/'><SOAP-ENV:Header></SOAP-ENV:Header><SOAP-ENV:Body><SOAP-ENV:Fault><faultcode>SOAP-ENV:Client</faultcode><faultstring>Error identifying Web Service: class com.sun.xml.internal.messaging.saaj.SOAPExceptionImpl: Unable to create envelope from given source: </faultstring></SOAP-ENV:Fault></SOAP-ENV:Body></SOAP-ENV:Envelope>

---

Ok that's better I think. At least you know you are sending an object to the server now. I don't really know much about SOAP I deal almost exclusively with json these days.

Some things stand out just looking at this but again I'm not well versed in this standard.

What is <soapenv...? When I look up SOAP structure it's defined just as <soap:Envelope... Not saying it's wrong just that it looks different than what I see.

Should xmlns:typ= actually be xmlns:type=?

Can the header be self closing?

EDIT: one other thing, the response you got back shows SOAP-ENV but you send SOAPENV? I don't know if that's of significance either just looking for oddities.

rbcs_2 said:

---

Ok that's better I think. At least you know you are sending an object to the server now. I don't really know much about SOAP I deal almost exclusively with json these days.

Some things stand out just looking at this but again I'm not well versed in this standard.

What is <soapenv...? When I look up SOAP structure it's defined just as <soap:Envelope... Not saying it's wrong just that it looks different than what I see.

Should xmlns:typ= actually be xmlns:type=?

Can the header be self closing?

EDIT: one other thing, the response you got back shows SOAP-ENV but you send SOAPENV? I don't know if that's of significance either just looking for oddities.

---

I used SOAPUI to generate the envelope from the WSDL, so I'm not sure exactly what everything does. My understanding is that soapenv and soap-env are namespace prefixes that could really be anything. The way I interpret it is that it's just aliasing and giving the namespace a shorter, easier to use name.
as for xlmns:typ, I've seen that in many other examples.


Also, I think I just got this to function...

I changed the way I built the string for the envelope. Instead of building it as one large string, is used strEnv=strEnv & "The next line of the envelope". For some reason, that made all the difference because now I get a response. It's odd to me because when I compare the two by printing the string output they're exactly the same.

Quote:

---

<SOAP-ENV:Envelope xmlns:SOAP-ENV='http://schemas.xmlsoap.org/soap/envelope/' xmlns:xsd='http://www.w3.org/2001/XMLSchema' SOAP-ENV:encodingStyle='http://schemas.xmlsoap.org/soap/encoding/' xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'><SOAP-ENV:Header></SOAP-ENV:Header><SOAP-ENV:Body><nis:GetJobResponse xmlns:nis='http://www.northgate-is.com/proiv/webservices/types'><returnArray><returnData>

<getJob>
</getJob>
</returnData></returnArray></nis:GetJobResponse></SOAP-ENV:Body></SOAP-ENV:Envelope>

---

Thanks for the help btw. If you're in San Antonio, I owe you a beer or two. Probably two.

No man, it's no problem. Since I don't mess with SOAP or VB/VBA, it took me a couple of reads to realize you were asking for help with the http request and not really anything else.

That's my fault. I wasn't sure what I needed to be asking because I'm not really familiar with it. Lo and behold the problem was in how I was building the envelope string and not the request itself, which is more VBA than http.