Recently working on a project, my team came across a requirement to call an Azure hosted WCF service from VBA (Visual Basic for Applications). VBA is the programming language used for writing macros in Office documents. We developed a code to call the rest service, which I’m sharing in this post.
Note that the WCF service is hosted on Azure – https://myservice.azurewebsites.net/Service.svc, and the request and response from Webservice are in JSON format.
Let’s look at the code snippet for calling a rest service in VBA.
Step 1: Add the below reference in your VBA project. Go to Tools->References and select Microsoft ActiveX Data Objects 2.8 Library. Click OK
Step 2: A common data request function is created, which based on the request type will perform GET or POST request.
1. In Data request method, we pass the Rest service URL and the postParameters list if it is a POST call. If the call is GET, the postParameters value will be blank.
2. Create object of MSXML2.XMLHTTP to carry out the web request.
3. Send the request to Web service.
The complete VBA code for data request method is as written below:
Function Datarequest(Url As String, Optional postParameters As String = “”) As String
Dim objHTTP as Object
Dim responseText As String
Dim requestType As String
If postParameters <> “” Then
requestType = “POST”
Else
requestType = “GET”
End If
Set objHTTP = CreateObject(“MSXML2.XMLHTTP”)
objHTTP.Open requestType, Url, False
objHTTP.setrequestheader “Content-Type”, “application/json”
If postParameters <> “” Then
objHTTP.send postParameters
Else
objHTTP.send
End If
responseText = objHTTP.ResponseText
Datarequest = responseText
End Function
Step 3: The GET request to the service is done by appending the parameter values in the URL separated by slash. Below is the code for GET request. Here ‘GetProjectGUID’ is the web method being called and ‘ProjectName’ is the parameter value.
Public Function GetProjectGUID(ByVal ProjectName As String) As String
Dim Result As String
Dim Url As String
Dim ProjectID As String
Url = WCFServiceUrl + “/GetProjectGUID/” & ProjectName
Result = Datarequest(Url)
End If
End Function
WCFServiceUrl is the Azure hosted service Url which will be like “https://testapp.azurewebsites.net/Service.svc”
This URL is then passed to Datarequest method created in the first step, which will return the result in the format specified in your web service (XML/JSON). I have used JSON format.
If the parameter is integer or boolean, then the webservice method will not accept it directly; it will be represented as query string. So, the request URL from VBA will also look like:
Url = WCFServiceUrl + “/MethodName/” & Parameter1Value & “/?Parameter2Name=” & Value
Parameter2Name is the parameter name mentioned in the web method in service and Value is the value that needs to be passed.
Step 4: The POST Request to the web service is done by passing the parameters list not in the URL but in the body itself. Below is the code for POST request:
Public Function CreateTask (ByVal TaskName As String, ByVal ProjectUID As String) As String
Dim Result As String
Dim Url As String
Dim postParameters As String
Url = WCFServiceUrl + “/ CreateTask “
postParameters = “{“” TaskName “”:” & Chr(34) & TaskName & Chr(34) & “,”” ProjectUID “”:” & Chr(34) & ProjectUID & Chr(34) & “}”
Result = Datarequest(Url, postParameters)
CreateTask = Result
End Function
The rest URL for POST method consists of Web service URL and the method name. The post parameters string is in JSON format.
The Webservice URL and postParameters are passed to Datarequest method created in the first step, which will return the result in the format specified in your web service (XML/JSON).
So, here you go. Hope you find it helpful!
