PDA

View Full Version : SOAP Calls In Excel 2007/2010



zennon
10-12-2011, 07:33 AM
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
10-12-2011, 01:13 PM
The search engine at this site finds: http://www.vbaexpress.com/forum/showthread.php?t=34354

zennon
10-13-2011, 01:13 AM
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.

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/services/USD_R11_WebService?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

Hope this helps.