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?

CodePudding user response：

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:

1. The string you show misses a ":" character (replaced with ";").Here: "area"; 232,. It should be correct to be parsed...

2. 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