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

问题描述

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

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-在此过程中，我正在使用 VBA-JSON ，它可以在MsgBox中成功显示"responseText".

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

推荐答案

在我对来自Wordpress API的数据 ，我编写了一个函数PrintJSONAccessors()，该函数详细介绍了如何访问JSON结构中的数据.

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.

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

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

在即时窗口"中检查项目的TypeName还会显示该项目确实是一个集合".

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

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

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屋！