How do we use restful APIs from Excel macros (vba)?

Question

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.

Answer 1

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 = "https://jsonplaceholder.typicode.com/posts/1"     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.