How Do We Use Restful Apis from Excel Macros (Vba)?
Is There a Plugin or Library That Could Be Used to Access Restful Apis from Excel (Probably Using Macros) and Then Store the Responses Somewhere (Probably in a...
Is there a plugin or library that could be used to access restful APIs from excel (probably using macros) and then store the responses somewhere (probably in a sheet).
Pardon the missing sample code. I'm not a VBA programmer.
2 Answers
You can use the MSXML library within VBA. Then you can create an XMlHTTP request and do a GET or POST etc. Here's a code sample below. It uses late binding i.e. no need to reference the library first:
Option Explicit
Sub Test_LateBinding()
Dim objRequest As Object
Dim strUrl As String
Dim blnAsync As Boolean
Dim strResponse As String
Set objRequest = CreateObject("MSXML2.XMLHTTP")
strUrl = ""
blnAsync = True
With objRequest
.Open "GET", strUrl, blnAsync
.SetRequestHeader "Content-Type", "application/json"
.Send
'spin wheels whilst waiting for response
While objRequest.readyState <> 4
DoEvents
Wend
strResponse = .ResponseText
End With
Debug.Print strResponse
End Sub
I'm using this testing website - JSONPlaceholder - to call a RESTful API. This is the response:
Note that I found that calls to this website with this method fail if you a) make a synchronous request, or b) use http not https.
Here, you can find a detailed example using REST API for getting the information from JIRA and loading into excel worksheet. The excel file with the macros can be downloaded from the post. Anyway, it includes a detail explanation on each step in case you want to do it your self for your specific purpose. It's not worth repeating here. I was looking for a similar solution and this is so far, the only post I have found that covers from end to end this problem.
Maya is a hardware enthusiast who tests and reviews smart home devices, smartphones, wearables, and audio gear. She focuses on practical consumer value and build quality.
