如何使用 VBA 从 url 导入 json 数据？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/43624105/
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 do you import json data from a url utilizing VBA?
提问by Chris
I have the following json array which you can easily access at the below url:
我有以下 json 数组，您可以通过以下网址轻松访问：
https://crowdfluttr.firebaseio.com/test/array.json
https://crowdfluttr.firebaseio.com/test/array.json
It has the following output:
它有以下输出：
{"-0p":{"date":"2015-01-01","string":"apple","value":1},"-1p":{"date":"2015-02-04","string":"banana","value":50},"-2p":{"date":"2015-02-03","string":"carrot","value":99},"-3p":{"date":"2015-02-02","string":"banana","value":20},"-4p":{"date":"2015-03-01","string":"banana","value":11},"-5p":{"date":"2015-04-01","string":"kiwi","value":23},"-6p":{"date":"2015-05-01","strawberry":"banana","value":10}}
I'd like to pull this json data from this url and then parse it to push into microsoft access.
我想从这个 url 中提取这个 json 数据，然后解析它以推送到 microsoft access。
I found resources explaining how to parse JSON (Parsing JSON, Parsing JSON in Excel VBA) but not pull it from a URL and then parseit
我找到了解释如何解析 JSON（解析 JSON，在 Excel VBA 中解析 JSON）的资源，但没有从 URL 中提取它然后解析它
回答by Axel Richter
I would use XMLHTTP to download the JSON.
我会使用 XMLHTTP 下载 JSON。
For parsing JSON with VBA see https://github.com/VBA-tools/VBA-JSON.
要使用 VBA 解析 JSON，请参阅https://github.com/VBA-tools/VBA-JSON。
Download the ZIP file. Extract the JsonConverter.bas. Open Excel and the VBA-editor with your VBA-project. Right click the VBA-project in Project Explorer and click Import File....Browse to the JsonConverter.basfile and import it. Make sure, you have included a reference to "Microsoft Scripting Runtime" via Tools-References.
下载 ZIP 文件。提取JsonConverter.bas. 使用您的 VBA 项目打开 Excel 和 VBA 编辑器。在项目资源管理器中右键单击 VBA 项目，然后单击Import File....浏览到该JsonConverter.bas文件并将其导入。确保您已通过Tools-References.
Example using your URL:
使用您的 URL 的示例：
Sub test()
Dim httpObject As Object
Set httpObject = CreateObject("MSXML2.XMLHTTP")
sURL = "https://crowdfluttr.firebaseio.com/test/array.json"
sRequest = sURL
httpObject.Open "GET", sRequest, False
httpObject.send
sGetResult = httpObject.responseText
MsgBox sGetResult
Dim oJSON As Object
Set oJSON = JsonConverter.ParseJson(sGetResult)
For Each sItem In oJSON
dItemDate = oJSON(sItem)("date")
sItemString = oJSON(sItem)("string")
vItemValue = oJSON(sItem)("value")
MsgBox "Item: " & sItem & " Date: " & dItemDate & " String: " & sItemString & " Value: " & vItemValue
Next
End Sub
This code will work for your sample JSONlike:
此代码适用于您的示例，JSON例如：
{"-0p":{"date":"2015-01-01","string":"apple","value":1},"-1p":{"date":"2015-02-04","string":"banana","value":50}, ... }
{"-0p":{"date":"2015-01-01","string":"apple","value":1},"-1p":{"date":"2015-02-04","string":"banana","value":50}, ... }
You will have to analyze the JSONyou get from httpObject.responseTextto adapt the code for getting values from other JSONstructures.
您将不得不分析JSON您从中获得的内容，httpObject.responseText以调整代码以从其他JSON结构中获取值。
回答by Gustav
You can study the answer hereand then look up VBA.CVRAPIwhich contains all necessary Json modules to retrieve data from a URL. Though created for another purpose, the Json modules are generic and can easily be reused.
您可以在此处研究答案，然后查找VBA.CVRAPI，其中包含从 URL 检索数据所需的所有 Json 模块。虽然创建是出于其他目的，但 Json 模块是通用的，可以轻松重用。
The demo form included demonstrates this. You should be able to adopt it to your URL for a test.
包含的演示表单演示了这一点。您应该能够将它用于您的 URL 进行测试。
