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.

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

Solution 2

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.

Comments

[Viral Patel]

Thanks Robin I'll try this today

[David Leal]

@Robin Mackenzie I understand the answer explains how to obtain some information from Jira, but also the question is about how to put in an excel sheet. I am wondering how the response obtained in Json can be used to write it in the sheet. Usually, jira user interacts with JIRA using JQL, I guess it would be useful to send a query and then to put the response in a sheet.

[Robin Mackenzie]

@DavidLeal - once you have the JSON you can parse/ manipulate/ update sheets etc - see this question for example: stackoverflow.com/questions/50068973/json-vba-parse-to-excel

[Rick Henderson]

Admittedly this example is a long, in-depth example using JIRA, but it looks like it covers a lot of things people ought to know. Great work David.

[Rick Henderson]

Nice solution Robin. I knew Excel VBA can pretty much do anything, and this saves me from having to learn JS again. Which would be incredibly useful of course, but not something I have time for right now.