Articles → VBA FOR EXCEL → Call Web Service In VBA

Call Web Service In VBA

In this article, we will call the web service in VBA

Sample Web Service

For this example, we will be using http://www.dneonline.com/calculator.asmx. In this service, we have 4 methods i.e. Add, Divide, Multiply, Subtract.

We will be using ‘Add’ method here.

Adding Reference

Before you write the code, it is important to add the reference of ‘Microsoft.XML, V3.0’. To do that

Click on ‘Tools – References…’.

Picture showing the References submenu inside the Tools menu

Click to Enlarge

A window will appear to add references. Select ‘Microsoft.XML, V3.0’.

Picture showing a window to add the reference

Click to Enlarge

Code

Following code is for calling the web service

Dim sMsg As String
Dim sURL As String
Dim sEnv As String

Set ObjHTTP = New MSXML2.XMLHTTP
sURL = "http://www.dneonline.com/calculator.asmx?op=Add"
sEnv = "<?xml version=""1.0"" encoding=""utf-8""?>"
sEnv = sEnv & "<soap12:Envelope xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" xmlns:soap12=""http://www.w3.org/2003/05/soap-envelope"">"
sEnv = sEnv & "<soap12:Body>"
sEnv = sEnv & "<Add xmlns=""http://tempuri.org/"">"
sEnv = sEnv & "<intA>1</intA>"
sEnv = sEnv & "<intB>2</intB>"
sEnv = sEnv & "</Add>"
sEnv = sEnv & "</soap12:Body>"
sEnv = sEnv & "</soap12:Envelope>"

ObjHTTP.Open "Post", sURL, False
ObjHTTP.setRequestHeader "Content-Type", "text/xml"
ObjHTTP.setRequestHeader "Action", "http://tempuri.org/Add"

ObjHTTP.Send (sEnv)
MsgBox ObjHTTP.responseText

Set ObjHTTP = Nothing
Set xmlDoc = Nothing

Output

Picture showing the response of the XML web service in the message box

Click to Enlarge

Posted By -	Karan Gupta

Posted On -	Sunday, October 21, 2018

Query/Feedback

Your Email Id		**

Subject		*

Query/Feedback	Characters remaining 250	**