如何获取 JSON 值以在 VBA-JSON 中工作? [英] How to get, JSON values to Work in VBA-JSON?

问题描述

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

API URL -

检查立即窗口中项目的 TypeName 也显示该项目确实是一个集合'

?TypeName(Item)收藏

<块引用>

PrintJSONAccessors JSON, "?JSON"

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

这里是您如何访问集合中的项目

对于JSON中的每个项目Sheets(1).Cells(i, 1).Value = Item(1) ' 以Items引用为例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)我 = 我 + 1下一个

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

公共子exceljson()将 http 作为对象变暗设置 http = CreateObject("MSXML2.XMLHTTP")http.Open "GET", "https://api.bitfinex.com/v2/candles/trade:5m:tEOSUSD/hist?start=1535760000000&end=1538265600000&sort=1", Falsehttp.发送变暗的结果作为变体结果 = BitfinexTextToArray(http.responseText)Worksheets(1).Range("A1").Resize(UBound(results), UBound(results, 2)).Value = resultsMsgBox ("完成")结束子函数 BitfinexTextToArray(responseText As String) As VariantDim item As Variant, JSON As ObjectDim MaxColumns 一样长设置 JSON = ParseJson(responseText)对于 JSON 中的每个项目如果 item.Count >MaxColumns 然后 MaxColumns = item.Count下一个变暗的结果作为变体ReDim 结果(1 到 JSON.Count，1 到 MaxColumns)Dim c As Long, r As Long对于 JSON 中的每个项目r = r + 1对于 c = 1 To item.Count结果(r，c)=项目(c)下一个下一个BitfinexTextToArray = 结果结束函数

I am trying to access nested JSON values that come back from the API that I am working with at the moment. There seem to be no field names to use in this JSON, making it very difficult to follow most examples online.

API URL - CLICK HERE

I am using VBA-JSON through this process, and I've got it to successfully display "responseText" in MsgBox.

I am looking for a way to make this code work.

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

解决方案

In my answer to Using VBA and VBA-JSON to access JSON data from Wordpress API , I wrote a function, PrintJSONAccessors(), which breaks down how to access the data in a JSON structure.

Checking the JSON object in the Locals Window reveals that it consists of a collection of collections.

Checking the TypeName of the item in the Immediate Window also reveals that item is indeed a collection'

?TypeName(Item)
Collection

PrintJSONAccessors JSON, "?JSON"

The code will output the correct way to access the data

Here is how you can access the items of the Collection

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

I would write a function to convert the JSON data into an Array

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

这篇关于如何获取 JSON 值以在 VBA-JSON 中工作?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！