How can I retrieve JSON from an HTTP endpoint, from within Excel on MacOS, and parse it?

Question

I have seen How can I send an HTTP POST request to a server from Excel using VBA?

and the MacOS-friendly response that describes how to retrieve data from an HTTP endpoint using QueryTables. It demonstrates how to retrieve a single string and stuff it into a cell.

All good. Now I would like to retrieve more than a single value. It's a large JSON string, and I want to post-process it within Excel VBA before populating one or more cells.

How is this possible?

I can think of one way - place the result of the QueryTables thing into a hidden cell, and then post-process the hidden cell to populate other cells. There are a few JSON libraries for VBA that I have not evaluated yet.

But this seems pretty hacky. Really I want to not rely on storing the JSON as a value in a cell. I'd like to store it only into a variable in my VBA code. Just as if I was using CreateObject("MSXML2.ServerXMLHTTP"). (NB: CreateObject() is not available from within Excel on MacOS).

And I understand that the best answer here might be: Get a Windows machine if you want to run apps within Excel.

Answer 1

you can acutally use the Worksheets(0).QueryTable method in VBA. Just have a look at the manual or google for it. So you don't have to store your json string into a cell.

Or I have used something like

Public Function GetWebSource(ByRef URL As String) As String
    Dim xml As IXMLHTTPRequest
    On Error Resume Next
    Set xml = CreateObject("Microsoft.XMLHTTP")
    With xml
        .Open "GET", URL, False
        .send
        GetWebSource = .responseText
    End With
    Set xml = Nothing
End Function

to download an json string.

Look around for parsers. Somehting like Parsing JSON in Excel VBA should fill your needs.