vba 如何从 HTTP 端点、MacOS 上的 Excel 中检索 JSON 并解析它？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14986015/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I retrieve JSON from an HTTP endpoint, from within Excel on MacOS, and parse it?
提问by Cheeso
I have seen How can I send an HTTP POST request to a server from Excel using VBA?
我已经看到如何使用 VBA 从 Excel 向服务器发送 HTTP POST 请求？
and the MacOS-friendly responsethat 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.
以及 描述如何使用 QueryTables 从 HTTP 端点检索数据的 MacOS 友好响应。它演示了如何检索单个字符串并将其填充到单元格中。
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.
都好。现在我想检索多个值。这是一个很大的 JSON 字符串，我想在填充一个或多个单元格之前在 Excel VBA 中对其进行后处理。
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.
我可以想到一种方法 - 将 QueryTables 的结果放入隐藏单元格中，然后对隐藏单元格进行后处理以填充其他单元格。有一些我尚未评估的 VBA JSON 库。
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).
但这似乎很hacky。我真的不想依赖将 JSON 作为值存储在单元格中。我只想将它存储到我的 VBA 代码中的一个变量中。就像我在使用 CreateObject("MSXML2.ServerXMLHTTP") 一样。（注意：CreateObject() 在 MacOS 上的 Excel 中不可用）。
And I understand that the best answer here might be: Get a Windows machine if you want to run apps within Excel.
我知道这里最好的答案可能是： 如果您想在 Excel 中运行应用程序，请购买一台 Windows 机器。
回答by mffap
you can acutally use the Worksheets(0).QueryTablemethod 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.
您可以Worksheets(0).QueryTable在 VBA 中使用该方法。只要看看手册或谷歌就可以了。因此，您不必将 json 字符串存储到单元格中。
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.
下载一个json字符串。
Look around for parsers. Somehting like Parsing JSON in Excel VBAshould fill your needs.
四处寻找解析器。像在 Excel VBA 中解析 JSON 之类的东西应该可以满足您的需求。
