Reputation: 29
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.
Upvotes: 2
Views: 523
Reputation: 84475
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.
Upvotes: 2