VBA-tools/VBA-JSON

JsonConverter.ParsonJson() Only Parsing First Record

xymantec opened this issue · 7 comments

xymantec commented

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 commented
xymantec commented

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 commented

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 commented

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 commented

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 commented

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 commented

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.