Fetch Data from Web Services in Excel Macro

Since the time I have started working in Enterprise Solutions. There is only one universal truth, everything can be done in Microsoft Excel. Murphy Law fits it completely, what can happen will happen in Excel.

Having said that, just wrote a small snippet to call Web Service from Excel Macro. The code is pretty self explanatory.

Private Sub FetchData()
Dim objRequest As Object, strUrl As String, blnAsync As Boolean, strResponse As String
Dim jsonObject As Object, item As Variant
Set objRequest = CreateObject("MSXML2.XMLHTTP")
strUrl = "<URL_GOES_HERE>"
blnAsync = True
With objRequest
.Open "GET", strUrl, blnAsync
.setRequestHeader "Content-Type", "application/json"
While objRequest.readyState <> 4
strResponse = .ResponseText
End With
End Sub

“strResponse” will store the return data in requested format”. In my case I had data coming in JSON format, there is however no native support to read that, I had to use a 3rd party JSON library

Dim jsonObject As Object, item As Variant
Set jsonObject = JsonConverter.ParseJson(strResponse)
For Each item In jsonObject
'do something with json object
Next item

Details on the Library @ https://github.com/VBA-tools/VBA-JSON