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"
}
}
]
}
}

 

[rkaczano]

Maybe a slightly different way of asking this is that I know that if I type in:

?Debug.Print TypeName (response)

I get “Dictionary” in the IMMEDIATE window. So I know the data structure is a dictionary.

So I want to print out the data in that structure into the IMMEDIATE window in 5 columns:

2000-01-01 07:00, 2000-01-01 00:00, $21.65, $30.48, $40.12

How do I do that? Clearly I am iterating through the dictionary but I am unclear how to do that.

Thanks

 

[frabulator]

What I had to do to get a correct response from my companies SOAP was format my outgoing string into an XML formatted string.

I dont know if it'll help, but please see below. I have modified only what I need to keep things secure.

Public ServRespon As String

Sub ConnectToServer(PN As String, Ls As String, Method_0 As Integer)
    ServRespon = ""
    
    Dim sURL As String
    Dim sEnv As String
    Dim xmlhtp As New MSXML2.XMLHTTP60
    Dim xmlDoc As New DOMDocument60
    sURL = "https://www.web.com/WebServiceSOAP/PHP.php/CSOAP"
    Dim HD As String
    HD = "FUR"


    sEnv = "<?xml version=""1.0"" encoding=""utf-8""?>"
    sEnv = sEnv & "<soap:Envelope xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" xmlns:soap=""http://schemas.xmlsoap.org/soap/envelope/"">"
    sEnv = sEnv & "  <soap:Body>"
    sEnv = sEnv & "    <CSOAP>"
    sEnv = sEnv & "      <ls>" & Ls & "</ls>"
    sEnv = sEnv & "      <pn>" & PN & "</pn>"
    sEnv = sEnv & "      <hd>" & HD & "</hd>"
    sEnv = sEnv & "      <method>" & Method_0 & "</method>"
    sEnv = sEnv & "    </CSOAP>"
    sEnv = sEnv & "  </soap:Body>"
    sEnv = sEnv & "</soap:Envelope>"

    
    Dim ci As Boolean
    ci = checkInternetConnection
    
    
    If ci = False Then
        MsgBox ("Error connecting to the server. Please verify that your have internet connection. If this error continues please contact your IT department or email our support at ____________________.")
    Else
        With xmlhtp
            .Open "post", sURL, False
            .setRequestHeader "Content-Type", "text/xml; charset=utf-8"
            .send sEnv
            xmlDoc.LoadXML .responseText
            ServRespon = xmlDoc.Text
        
        End With
    End If
    
    

End Sub



Function checkInternetConnection() As Integer
    On Error Resume Next
    checkInternetConnection = False
    Dim objSvrHTTP As ServerXMLHTTP60
    Dim varProjectID, varCatID, strT As String
    Set objSvrHTTP = New ServerXMLHTTP60
    objSvrHTTP.Open "GET", "https://www.google.com"
    objSvrHTTP.setRequestHeader "Accept", "application/xml"
    objSvrHTTP.setRequestHeader "Content-Type", "application/xml"
    objSvrHTTP.send strT
    If Err = 0 Then
        checkInternetConnection = True
    Else
        checkInternetConnection = False
    End If
End Function

I know at best this was finicky, so I ended up opting for a VB.Net application to connect to my SOAP. It was much easier, and I could use a temp text file to communicate back and forth with Excel. The only downside to this step is you need to have a second software tool to modify it.

 

[RoryA]

Maybe a slightly different way of asking this is that I know that if I type in:

?Debug.Print TypeName (response)

A more relevant test would be:

?TypeName(response("Pool Price Report"))

I suspect it is not an object.

 

[rkaczano]

A more relevant test would be:

?TypeName(response("Pool Price Report"))

I suspect it is not an object.

RoryA, you are correct when I change the Object test from Debug.Print TypeName (response) to Debug.Print TypeName (response("Pool Price Report")) I do in fact receive an error. What I cannot figure out is why that is the case given the JSON structure below.

And again the intent was to take the data in the request.ResponseText and ultimately dump it to the IMMEDIATE window and/or Excel.

Can you tell me by looking at the JSON data below how to infer what the dictionary structure is so that I can loop through it? Cleary I am trying to identify the key/items.

{
"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

 

[RoryA]

What's the code for the JsonConverter.ParseJson function?

 

[rkaczano]

Its based on the following:

GitHub - VBA-tools/VBA-JSON: JSON conversion and parsing for VBA

JSON conversion and parsing for VBA. Contribute to VBA-tools/VBA-JSON development by creating an account on GitHub.

github.com

Thanks

 

[rkaczano]

No takers on this? I was assuming there was an easy answer to this.

To be clear I am not looking how to parse the JSON code as that has already occurred here. The immediate window shows the parsed JSON structure via the request.ResponseText and I am simply just looking to extract the data I need from that parsed JSON response.

Thanks

 

[John_w]

"return": {
"Pool Price Report": [

The "Pool Price Report" array (Collection) is accessed via the "return" key. Therefore:

    Dim prices As Collection, i As Long
    
    Set prices = response("return")("Pool Price Report")
    For i = 1 To prices.Count
        Debug.Print prices(i)("begin_datetime_utc")
        Debug.Print prices(i)("pool_price")
    Next

 

[rkaczano]

Yes that works. Dumb question - why do you have to include the term ("return") prior to referencing ("Pool Price Report")?

Set prices = response("return")("Pool Price Report")

Thanks