arrays - 如何获取在 VBA-JSON 中工作的 JSON 值？

Question

我正在尝试访问从我目前正在使用的 API 返回的嵌套 JSON 值。此 JSON 中似乎没有可使用的字段名称，因此很难遵循大多数在线示例。

API URL - CLICK HERE

我正在使用VBA-JSON通过这个过程，我已经成功地在 MsgBox 中显示“responseText”。

我正在寻找一种方法来使该代码正常工作。

Public Sub exceljson()
    Dim http As Object, JSON As Object, i As Integer
    Set http = CreateObject("MSXML2.XMLHTTP")
    http.Open "GET", "https://api.bitfinex.com/v2/candles/trade:5m:tEOSUSD/hist?start=1535760000000&end=1538265600000&sort=1", False
    http.Send
    Set JSON = ParseJson(http.responseText)
    i = 2
    For Each Item In JSON
        Sheets(1).Cells(i, 1).Value = Item("one")  ' Items reference as an example
        Sheets(1).Cells(i, 2).Value = Item("two")
        Sheets(1).Cells(i, 3).Value = Item("three")
        Sheets(1).Cells(i, 4).Value = Item("four")
        Sheets(1).Cells(i, 5).Value = Item("five")
        i = i + 1
    Next
    MsgBox ("complete")
End Sub

Answer 1

在我对 Using VBA and VBA-JSON to access JSON data from Wordpress API 的回答中，我编写了一个函数 PrintJSONAccessors()，它详细说明了如何访问 JSON 结构中的数据。

在本地窗口中检查 JSON 对象会发现它由一组集合组成。

在立即窗口中检查项目的 TypeName 还会发现该项目确实是一个集合”

?TypeName(Item)
Collection

PrintJSONAccessors JSON, "?JSON"

代码将输出访问数据的正确方式

您可以通过以下方式访问收藏中的元素

For Each Item In JSON
    Sheets(1).Cells(i, 1).Value = Item(1)     ' Items reference as an example
    Sheets(1).Cells(i, 2).Value = Item(2)
    Sheets(1).Cells(i, 3).Value = Item(3)
    Sheets(1).Cells(i, 4).Value = Item(4)
    Sheets(1).Cells(i, 5).Value = Item(5)
    i = i + 1
Next

我会编写一个函数将 JSON 数据转换为数组

Public Sub exceljson()
    Dim http As Object
    Set http = CreateObject("MSXML2.XMLHTTP")
    http.Open "GET", "https://api.bitfinex.com/v2/candles/trade:5m:tEOSUSD/hist?start=1535760000000&end=1538265600000&sort=1", False
    http.Send

    Dim results As Variant
    results = BitfinexTextToArray(http.responseText)

    Worksheets(1).Range("A1").Resize(UBound(results), UBound(results, 2)).Value = results

    MsgBox ("complete")
End Sub

Function BitfinexTextToArray(responseText As String) As Variant
    Dim item As Variant, JSON As Object
    Dim MaxColumns As Long

    Set JSON = ParseJson(responseText)

    For Each item In JSON
        If item.Count > MaxColumns Then MaxColumns = item.Count
    Next

    Dim results As Variant
    ReDim results(1 To JSON.Count, 1 To MaxColumns)

    Dim c As Long, r As Long
    For Each item In JSON
        r = r + 1

        For c = 1 To item.Count
            results(r, c) = item(c)
        Next
    Next

    BitfinexTextToArray = results
End Function