Cannot Pass API response back to Collection in VBA

[rkaczano]

I am running an API request in VBA using the the WinHttpRequest object.

The API call successfully makes the request and the reply from the website (JSON format) is parsed and passed to a "response" object in VBA.

************
Dim response As Object
Set response = JsonConverter.ParseJson(request.ResponseText)

The problem I am having is that I cannot seem to pass the data in the response object into a collection/dictionary object so that I can loop through it. I can see the data in the .ResponseText property of the request object (See IMMEDIATE WINDOW below) and I also can see the JSON structure (see results below).

When I try to pass the response object to a collection, I get a Runtime 424 error (Object Required).

Where is the error in my syntax?

**********
' Access the data
Dim price As Collection
Set price = response("Pool Price Report")


IMMMEDIATE WINDOW
?request.ResponseText
{"timestamp":"2023-02-07 22:49:38.728+0000","responseCode":"200","return":{"Pool Price Report":[{"begin_datetime_utc":"2000-01-01 07:00","begin_datetime_mpt":"2000-01-01 00:00","pool_price":"21.65","forecast_pool_price":"30.48","rolling_30day_avg":"40.12"},{"begin_datetime_utc":"2000-01-01 08:00","begin_datetime_mpt":"2000-01-01 01:00","pool_price":"21.00","forecast_pool_price":"21.0","rolling_30day_avg":"40.12"},{"begin_datetime_utc":"2000-01-01 09:00","begin_datetime_mpt":"2000-01-01 02:00","pool_price":"5.90","forecast_pool_price":"18.5","rolling_30day_avg":"40.10"},{"begin_datetime_utc":"2000-01-01 10:00","begin_datetime_mpt":"2000-01-01 03:00","pool_price":"5.85","forecast_pool_price":"5.9","rolling_30day_avg":"40.08"},{"begin_datetime_utc":"2000-01-01 11:00","begin_datetime_mpt":"2000-01-01

JSON
{
"timestamp": "2023-02-07 15:47:16.263+0000",
"responseCode": "200",
"return": {
"Pool Price Report": [
{
"begin_datetime_utc": "2000-01-01 07:00",
"begin_datetime_mpt": "2000-01-01 00:00",
"pool_price": "21.65",
"forecast_pool_price": "30.48",
"rolling_30day_avg": "40.12"
},
{
"begin_datetime_utc": "2000-01-01 08:00",
"begin_datetime_mpt": "2000-01-01 01:00",
"pool_price": "21.00",
"forecast_pool_price": "21.0",
"rolling_30day_avg": "40.12"
},
{
"begin_datetime_utc": "2000-01-01 09:00",
"begin_datetime_mpt": "2000-01-01 02:00",
"pool_price": "5.90",
"forecast_pool_price": "18.5",
"rolling_30day_avg": "40.10"
},
{
"begin_datetime_utc": "2000-01-01 10:00",
"begin_datetime_mpt": "2000-01-01 03:00",
"pool_price": "5.85",
"forecast_pool_price": "5.9",
"rolling_30day_avg": "40.08"
}
}
]
}
}

 

[John_w]

Because, "Pool Price Report" is inside "return".