You can call a webservice from VBA simply by posting the soap envelope. Here is an example:
Dim http As New WinHttp.WinHttpRequest Dim URL As String Dim envelope As String URL = "http://notificationserver/NotificationServer/NotifyService" envelope = "<?xml version=""1.0"" encoding=""UTF-8""?><soap:Envelope soap:encodingStyle=""http://schemas.xmlsoap.org/soap/encoding/"" xmlns:soap=""http://schemas.xmlsoap.org/soap/envelope/"" xmlns:soapenc=""http://schemas.xmlsoap.org/soap/encoding/"" xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" xmlns:tns=""http://NotificationServer.nvidia.com/""><soap:Body><tns:notify><id xsi:type=""xsd:string"">blah</id><from xsi:type=""xsd:string"">from </from><subject xsi:type=""xsd:string"">subject</subject><details xsi:type=""xsd:string"">details</details></tns:notify></soap:Body></soap:Envelope>" Call http.Open("POST", URL) http.SetRequestHeader "Content-Type", "text/xml" http.SetRequestHeader "SOAPAction", " " http.Send envelope MsgBox http.ResponseText
Hi,
What if you need to send some binary data to the web service as well? I guess the code above would not work… any example on how do to that?
Thanks,
Luiz
Comment by Luiz — January 23, 2009 @ 5:10 pm
I tried pasting the above into a macro in excel (between Sub() and End Sub). But, the above appears to not be complete. I think the project must also need some references or other definitions? Any chance you can post a complete example, like a cookbook, do this, then this…
Thanks!
Comment by Tom — April 26, 2011 @ 9:33 pm
Set osoap = CreateObject(“MSSOAP.SoapClient”)
osoap.ClientProperty(“ServerHTTPRequest”) = True
osoap.mssoapinit (“http://192.168.2.30:5050/Selling/soap/description?WSDL”)
result = osoap.get_customer_debit_balance(CUSTOMERID)’this function return type is float and it is working fine
Dim result1 As object
result1 = osoap.get_customer_balance(1010) ‘this function return type is CustomerBalance class object
MsgBox result1
I am running this code and getting invalid procedure or function call error message
when I am using PHP with SOAP and this function returning array but in VB i am getting error.
please help me to fix up this problem and explain me how to get multiple values from a function using VB.
Comment by Anonymous — March 21, 2012 @ 11:33 am
What library do you reference here?
Comment by jim — July 22, 2012 @ 4:41 am
[…] https://developerresources.wordpress.com/2008/06/13/vbvba-call-webservice/ […]
Pingback by How to fetch data from Web Service in VBA-Word? - DexPage — July 6, 2015 @ 5:52 am
I’m trying to set a simple macro to search for selected text in any given search engine in a specified browser from Word using a macro set up using VBA in Word.
Comment by TLEE — January 14, 2020 @ 1:46 am