Calling Azure Rest Service from VBA

Calling Azure Rest Service from VBA

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 –, 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"
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
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 “”

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!

Let's connect to make technology work for you.

Project Portfolio ManagementCloud AdoptionAdaptive BIDigital WorkspaceConsulting

Posted by Supriya

Supriya is currently working as an EPM Engineer with Advaiya, and has more than 7 years of experience in applications development. Her responsibilities include project development, deployment, troubleshooting, client communication, etc. Microsoft Certified Professional in EPM, Supriya has extensive knowledge in technologies including - Sharepoint,Enterprise Project Server,SSRS,among others.

Related Posts:

No Comments

Leave a Reply

Your email address will not be published.


fifteen − thirteen =