json - 如何使用 VBA 从 url 导入 json 数据？

Question

我有以下 json 数组，您可以通过以下网址轻松 Access 它:

https://crowdfluttr.firebaseio.com/test/array.json

它有以下输出:

{"-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}}

我想从这个 url 中提取这个 json 数据，然后解析它以推送到 Microsoft Access。

我找到了解释如何解析 JSON(Parsing JSON、Parsing JSON in Excel VBA)但不是从 URL 中提取然后解析的资源

Answer 1

我会使用 XMLHTTP 下载 JSON。

有关使用 VBA 解析 JSON，请参阅 https://github.com/VBA-tools/VBA-JSON .

下载 ZIP 文件。提取 JsonConverter.bas。使用您的 VBA 项目打开 Excel 和 VBA 编辑器。在 Project Explorer 中右键单击 VBA 项目，然后单击 Import File... 浏览到 JsonConverter.bas 文件并将其导入。确保您已通过 Tools-References 包含对“Microsoft Scripting Runtime”的引用。

使用您的 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

此代码适用于您的示例 JSON，例如:

{"-0p":{"date":"2015-01-01","string":"apple","value":1},"-1p":{"date": "2015-02-04","string":"banana","value":50}, ...

您必须分析从 httpObject.responseText 获取的 JSON 以调整代码以从其他 JSON 结构获取值。