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.

Solution

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.

Comments

[Cheeso]

Ya, I know I can use QueryTables. But I have googled and I don't see a way to store the output of QueryTable into anything except a cell. That feels pretty hacky to me, which is why I asked for an alternative. If you know a way to store the value into a variable, I will appreciate it if you can describe that or refer me to the doc link. Also the Microsoft.XMLHTTP thing won't work from within Excel on MacOS.

[mffap]

Uff, that's gonna be tough. I think you're right. QueryTable only stores its contents into an cell (that's in fact the whole point of the function, since it is only a table with connected datasource). Personally, I only know the way above or with an DLL, but that's also windows. Still, Excel is built to use on windows. If you want all the features, use boot camp. Another way would be to use a tool like curl and execute via shell.

[mango]

what a really great method. Do you know if the function result might ever be too large to return? too large a string?

[mffap]

since this function is a basic functionality of IE, the answer depends on many parameters, I reckon. Here is a discussion about a similar answer stackoverflow.com/questions/124742/… . Personally, I haven't had a problem - yet!

[dgo]

Sweet! I got it to work straight away. I have to parse it now.