Access Values In Nested Array in JSON response

Question

I have a multidimensional JSON array returned via a web request (http://www.coincap.io/history/365day/BTC). I want to cycle through the 2nd entry and retrieve its nested values.

If this was a normal array, I'd use:

For Each item In response
    logic, logic, logic

    currentRow = currentRow + 1
Next

This web request returns a JSON-response with 3 entries: market_cap, price, and volume. I just want to cycle through response(1) and get the price values. Each entry in price contains two keys, 0 and 1.

I would imagine that I could accomplish this by doing

For Each item in response(1)
    Cells(currentRow, 1).Value = item(0)
    Cells(currentRow, 2).Value = item(1)

    currentRow = currentRow + 1
Next

I've also considered For Each item in response("price"). Neither works.

Please show/describe exactly how you got to your response object. Are you using a JSON-parsing library/class? If Yes, which one? If No then....? It's much easier for folk to help you if you provide a working set of code to get them at least as far as you have got. — Tim Williams
– Tim Williams, Commented Jun 9, 2017 at 0:20

Tim Williams · Accepted Answer · 2017-06-09 00:54:56Z

1

Sub Tester()

    Dim json As String
    Dim sc As Object
    Dim o, n, i, p

    Set sc = CreateObject("scriptcontrol")
    sc.Language = "JScript"

    json = HttpGet("http://www.coincap.io/history/365day/BTC")

    sc.Eval "var obj=(" & json & ")" 'evaluate the json response
    'add a couple of accessor functions
    sc.AddCode "function numPrices(){return obj.price.length;}"
    sc.AddCode "function getPrice(i){return obj.price[i];}"

    n = sc.Run("numPrices")
    For i = 0 To n - 1
        p = Split(sc.Run("getPrice", i), ",")
        Debug.Print i, p(0), p(1)
    Next i

End Sub

Function HttpGet(url As String) As String
    Dim oHTML As Object
    Set oHTML = CreateObject("Microsoft.XMLHTTP")
    With oHTML
        .Open "GET", url, False
        .send
        HttpGet = .responsetext
    End With
End Function

answered Jun 9, 2017 at 0:54

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Access Values In Nested Array in JSON response

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related