CODEWITHSUNDEEP
arraysjsonexcelvbaparsing
Smith O.
Smith O.

Reputation: 217

How to get, JSON values to Work in VBA-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 - 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

Upvotes: 3

Views: 6014

Answers (1)

TinMan
TinMan

Reputation: 7759

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.

enter image description here

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

enter image description here

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

Upvotes: 3

Related Questions