JsonConverter.ParsonJson() Only Parsing First Record

[xymantec]

Hi,

I am trying to parse a JSON object that has multiple objects within it. It appears the function cannot parse anything after the first record in the JSON object. Perhaps I am not using the function correctly but I don't think that is the case. Please advise.

Thanks.

[aholden10]

Example JSON and code??? What have you already tried???

On Sun, Aug 14, 2022 at 12:24 PM, Miguel @.***> wrote:

Hi,

I am trying to parse a JSON object that has multiple objects within it. It appears the function cannot parse anything after the first record in the JSON object. Perhaps I am not using the function correctly but I don't think that is the case. Please advise.

Thanks.

— Reply to this email directly, view it on GitHub, or unsubscribe. You are receiving this because you are subscribed to this thread.Message ID: @.***>

[xymantec]

I'll get back to you soon. I am still looking into the issue. I might have a malformed JSON string. PHP and Microsoft SQL can be buggy.

[xymantec]

Hi,

So here an example block of code:

1. We create an object

    Dim objRequest As Object
    Dim strUrl As String
    Dim blnAsync As Boolean
    Dim strResponse As String

    Set objRequest = CreateObject("MSXML2.XMLHTTP")

   'our URL with JSON output
    strUrl = "https://pokeapi.co/api/v2/pokemon/pikachu"

    blnAsync = True

    With objRequest
        .Open "GET", strUrl, blnAsync
        .setRequestHeader "Content-Type", "application/json"
        .send
        'spin wheels whilst waiting for response

        While objRequest.readyState <> 4
            DoEvents

        Wend
        strResponse = .responseText

    End With

    Dim Json As Object
    Set Json = JsonConverter.ParseJson(strResponse)

    Debug.Print Json("id")
    Debug.Print Json("abilities")(0)("ability")("name")

Unless I am trying to access the array incorrectly, the first debug.Print should display the number "25" the second debug.Print should display the string "static" but Instead, I get a "method or data member not found" error/notice.

if we change the strUrl to:

strUrl = "https://jsonplaceholder.typicode.com/posts/1"

and try to print it out:

Debug.Print Json("id")

we have no issue.

[Nick-vanGemeren]

Unless I am trying to access the array incorrectly

Exactly. JSON 'arrays' become Collections in VBA with the first element having index (not subscript) 1.

You may find debugging easier if you avoid complex one-liners. For example, going through the Collection with a For Each. This also allows viewing the current data structure level in the Locals window.

If this solves your issue, please close it.

[xymantec]

Unless I am trying to access the array incorrectly

Exactly. JSON 'arrays' become Collections in VBA with the first element having index (not subscript) 1.

You may find debugging easier if you avoid complex one-liners. For example, going through the Collection with a For Each. This also allows viewing the current data structure level in the Locals window.

If this solves your issue, please close it.

Ok, thanks for your feedback.

[Nick-vanGemeren]

I forgot a couple of remarks on the web access. Unless there is something else happening in parallel, you can drop the CPU-eating wheel spinning in favour of a synchronous call ( blnAsync = False) You should test the response status and take appropriate action if an error occurs.

[xymantec]

I forgot a couple of remarks on the web access. Unless there is something else happening in parallel, you can drop the CPU-eating wheel spinning in favour of a synchronous call ( blnAsync = False) You should test the response status and take appropriate action if an error occurs.

Thanks for the tip.