Send API Requests Using Excel VBA

I fell in love with excel macros since the first year of my job. It was simple, easy to learn and most of all it already had a pseudo database (i.e. the sheets). The challenge in front of me was to connect to a couple of APIs using excel. I know, I know Java or any other new age language would have come in useful, but this was something which I wanted to try for the sheer joy of it. 

So here comes the code…

Once the marco editor is opened, navigate to Tools à references and ensure that the below are selected 

Next up, the basic connection details. There are a couple of ways to connect either by using WINHTTP objects or IE object or a MSXML2.ServerXMLHTTP object which we will follow in this example. It is to this object that we will be adding the header, method, URL and body before sending it. Below is the code

Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
URL = "http://yourserver:8080/api/path1"
objHTTP.Open "POST", URL, False
objHTTP.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
sampleBody = “{ " & Chr(34) & "name" & Chr(34) & ":" & Chr(34) & "John" & Chr(34) & ", " & Chr(34) & "age" & Chr(34) & ":30, " & Chr(34) & "car" & Chr(34) & ":null }”
objHTTP.send sampleBody

Let’s look at each line

Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP") - This is where we create the object 

objHTTP.setRequestHeader "Content-Type", "application/x-www-form-urlencoded" – Set the request headers

objHTTP.send sampleBody – This command sends the request with the body. One thing of interest is how the body is constructed. Since there are no escape sequences in VBA, each double quotes has to be replaced with Chr(34). Of all the requests, building the body is what takes the most time as even a single missing quote may fail the entire request.

Once the request is sent, the ServerXMLHTTP object comes back with a response body, HTTP status code and status text

From this point onwards, it is only a matter of building the logic and sending the correct sequence of APIs to the server. The beauty of using VBA is storage and retrieval of data is very simple especially when using the sheets. One can even get the response time for requests using Timers.