macro – Analects of an Otaku

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"
	.Send
	While objRequest.readyState <> 4
	DoEvents
	Wend
	strResponse = .ResponseText
	End With
	End Sub

view raw GET_REQ_VIA_MACRO.vb hosted with ❤ by GitHub

“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

view raw read_json_in_macrocode.vb hosted with ❤ by GitHub

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

Tag: macro

Fetch Data from Web Services in Excel Macro