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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.