How to get data from JSON nested arrays in excel vba-json

Question

I'm preparing some kind of software working with Excel with an API, and I'm using vba-json library, and I dont know how to get data from nested arrays on JSON.

I've tryied some tutorials and another similar questions that I fond here. But every time I try I get a different error. Runtime error 5, 9, 13. Tryied different ways to accés the data but everytime I get error when I get into an array.

Set MyRequest = CreateObject("WinHttp.WinHttpRequest.5.1")
    MyRequest.Open "GET", "https://pokeapi.co/api/v2/pokemon/ditto/?fbclid=IwAR2pJwAgODOlI-Gdn8pH-RDFCcUfQWiYLZIVCnP8e-V_9gEwYymqRldpiFk"
    MyRequest.Send

    Dim Json As Object
    Set Json = JsonConverter.ParseJson(MyRequest.ResponseText)
    MsgBox Json("stats")("id")(0)("base_stat")

I'd like to get the data from the selected array to later be able to work with it, for example from

("stats")("id")(0)("base_stat")

get

48

The Json source is on the code.

QHarr · Accepted Answer · 2019-07-07T21:59:09.020

So, with JSON the [] denotes a collection you can For Each over and access by index, the {} indicates dictionaries you can For Each the dict keys of, or access items by key.

You can get a feel for the structure by pasting it into a json viewer such as this. You will need to familiarise yourself with reading json but you will see that, for that value you want, it has a path of:

json("stats")(1)("base_stat")

Note: The indexing starts at one for collections in VBA JSON though it is displays 0 in the viewer.

Reading the structure:

Option Explicit

Public Sub test()
    Dim Json As Object
    With CreateObject("WinHttp.WinHttpRequest.5.1")
        .Open "GET", "https://pokeapi.co/api/v2/pokemon/ditto/?fbclid=IwAR2pJwAgODOlI-Gdn8pH-RDFCcUfQWiYLZIVCnP8e-V_9gEwYymqRldpiFk"
        .send
        Set Json = JsonConverter.ParseJson(.responseText)
    End With
    MsgBox Json("stats")(1)("base_stat")
End Sub

Note there are various answers on StackOverflow which provide code that will list the access paths to every item in a json structure. In particular, I remember this great answer from @tinman here. It's not a substitute for learning to read JSON but an excellent tool for aiding and checking your understanding.

How to get data from JSON nested arrays in excel vba-json

1 Answers1