如何在MacOS上的Excel中从HTTP端点检索JSON并进行解析? [英] How can I retrieve JSON from an HTTP endpoint, from within Excel on MacOS, and parse it?

问题描述

我看过和 MacOS友好的响应，其中描述了如何使用QueryTable从HTTP端点检索数据.它演示了如何检索单个字符串并将其填充到单元格中.

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.

一切都很好.现在，我想获取的不仅仅是一个值.这是一个很大的JSON字符串，我想在填充一个或多个单元格之前在Excel VBA中对其进行后处理.

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.

这怎么可能?

我可以想到一种方法-将QueryTables的结果放入隐藏的单元格中，然后对隐藏的单元格进行后处理以填充其他单元格.有一些我尚未评估的VBA JSON库.

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.

但这似乎很hacky.确实，我不想依赖于将JSON作为值存储在单元格中.我只想将其存储到我的VBA代码中的变量中.就像我在使用CreateObject("MSXML2.ServerXMLHTTP")一样. (注意:在MacOS上的Excel中，无法使用CreateObject().)

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

我知道最好的答案是:如果要在Excel中运行应用程序，请获取Windows计算机.

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

推荐答案

您可以在VBA中手动使用Worksheets(0).QueryTable方法.只是看看手册或谷歌.因此，您不必将json字符串存储到单元格中.

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.

或者我使用了

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

下载json字符串.

to download an json string.

四处寻找解析器. 在Excel VBA中解析JSON 之类的东西可以满足您的需求.

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

这篇关于如何在MacOS上的Excel中从HTTP端点检索JSON并进行解析?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！