Parse multiple JSON/VBA objects with VBA-JSON

Question

I have the following JSON response:

[ { "id": 1354345345, "date": "2021-10-01T23:29:42.000000+02:00", "count": 0, "year": 2020, "area": 232, "numberList": [ 8693978 ], "Property": { "PropertyID": 135005860000118, "PropertyNumber": 2244 } }, { "id": 2345235345, "date": "2021-02-13T13:40:30.000000+01:00", "count": 2, "year": 2020, "area": 122, "numberList": [ 8693978 ], "Property": { "PropertyID": 153005860001536, "PropertyNumber": 1555 } } ]

Inside the array [], there can be several object {}, each with a value in "area". In this example, there are two objects in the array.

I am trying to parse the JSON via VBA-JSON.

I've tried the following VBA code, but it will only return the names of each object item and not the value.

Set Json = JsonConverter.ParseJson(responseText) For Each Item In Json For Each i In Item Debug.Print i Next Next

VBA debug console will show:

id date count year area numberList Property id date count year area numberList Property

How do I fetch the area of each object?

So, do you need the value of the two existing "area" keys? (232, 122)? — FaneDuru
– FaneDuru, Commented Aug 11, 2022 at 11:35

FaneDuru · Accepted Answer · 2022-08-11 11:52:13Z

JsonConverter.ParseJson(responseText) creates an object keeping Scripting Dictionaries... So, they expose keys and items. Based on that logic, please try the next way of "area" values extracting:

The string you show misses a ":" character (replaced with ";").Here: "area"; 232,. It should be correct to be parsed...
The next code version will iterate between the object keys and items, extracted the value (item) for the key "area":

 Dim json As Object, strFile As String, responseText As String, dict, i As Long responseText = "the corrected string you show..." Set json = JsonConverter.ParseJSON(responseText) For Each dict In json For i = 0 To dict.count - 1 If dict.Keys()(i) = "area" Then Debug.Print TypeName(dict), TypeName(dict.Keys()(i)), TypeName(dict.Items()(i)) Debug.Print dict.Items()(i) End If Next i Next dict

The ";" was a typo - my bad! Code works perfect, thank you for the explanation and code example!
@2by I was supposing it was a typo, but I must confess that I couldn't imagine how to copy it (probably, returned by code) and having a typo... :)
I removed a lot of the response to make it simpler, must have happened in the process :)

Collectives™ on Stack Overflow

Parse multiple JSON/VBA objects with VBA-JSON

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related