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

2

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

edited Aug 11, 2022 at 11:52

answered Aug 11, 2022 at 11:42

FaneDuru

42.3k4 gold badges22 silver badges30 bronze badges

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

3 Comments

2by Over a year ago

The ";" was a typo - my bad! Code works perfect, thank you for the explanation and code example!

FaneDuru Over a year ago

@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... :)

2by Over a year ago

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

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related