rest - 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.