SOAP Calls In Excel 2007/2010

[zennon]

Hi,

Is it possible to send SOAP calls through VBA in either Excel 2007 ro 2010? I have checked various sites and can't see if you can. If this can be done, HOW?

Thanks

[Kenneth Hobs]

The search engine at this site finds: http://www.vbaexpress.com/forum/showthread.php?t=34354

[zennon]

Hi,

I managed to get a start from another thread. I have now managed to get the SOAP call to work. The calls are going to the CA Service desk software. So for any one else trying to send SOAP from VBA code is below. Just change the sURL addresses to mach your needs.

You will need to add reference to the Microsoft XML, v6.0. TOOLS - REFERENCE in the VBA screen.

[VBA]Option Explicit

'Set Reference to Microsoft XML, v6.0
Sub login()
Dim responseText As String
Dim sURL As String
Dim sEnv As String
Dim xmlhtp As New MSXML2.XMLHTTP
Dim xmlDoc As New DOMDocument
Dim startPos, endPos, openTag, closeTag, startTagPos, sid As String
sURL = "http://<your server>:8080/axis/servi...ebService?wsdl"
sEnv = "<?xml version=""1.0"" encoding=""utf-8""?>"
sEnv = sEnv & "<soap:Envelope xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" xmlns:soap=""http://schemas.xmlsoap.org/soap/envelope/"">"
sEnv = sEnv & " <soap:Body>"
sEnv = sEnv & " <login xmlns=""http://www.ca.com/UnicenterServicePlus/ServiceDesk"">"
sEnv = sEnv & " <username></username>"
sEnv = sEnv & " <password></password>"
sEnv = sEnv & " </login>"
sEnv = sEnv & " </soap:Body>"
sEnv = sEnv & "</soap:Envelope>"
With xmlhtp
.Open "post", sURL, False
.setRequestHeader "Host", "webservices.gama-system.com"
.setRequestHeader "Content-Type", "text/xml; charset=utf-8"
.setRequestHeader "soapAction", "http://www.ca.com/UnicenterServicePlus/ServiceDesk"
.setRequestHeader "Accept-encoding", "zip"
.send sEnv
xmlDoc.LoadXML .responseText
responseText = .responseText
End With
openTag = "<loginReturn"
closeTag = "</loginReturn>"
startPos = InStr(1, responseText, openTag) + 10
endPos = InStr(1, responseText, closeTag)
startTagPos = InStr(startPos, responseText, ">") + 1
' Parse xml for returned value
sid = Mid(responseText, startTagPos, endPos - startTagPos)
'Call Next Web Service
getUser
End Sub[/VBA]

Hope this helps.