How to read json file and take in excel using vba

Question

I have an excel cell value [[{"Name":Ashwin ,"Age":64}],[],[{"Name":Shakur ,"Age":64,"Gender":Male}]]

I need to display the value of gender in cells.

Please find below my code:

Option Explicit

Sub ExampleSplit()
    Dim s As String, vx() As String
    My_array = Worksheets("sheet1").Cells(1, 1)
    vx = Split(My_array, "{")
    Array_need = "{" & Split(vx(UBound(vx)), "}")(0) & "}"
    Set Jsonobject = JsonConverter.ParseJson(Array_need)
    
    For Each Item In Jsonobject
        If Item = "Gender" Then
            Worksheets("sheet1").Cells(1, 2) = Item("Gender")
        End If
    Next

End Sub

After running sucessfuly,value "Male" should be in worksheets("sheet1").cells(1,2).But for me it was throwing "type mismatch"

Answer 1

I wrote PrintJSONAccessors() to answer a similar question: Using VBA and VBA-JSON to access JSON data from Wordpress API. My sub routine prints the proper way to access the json data to the Immediate Window.

The sample code is not valid JSON. It is missing double quotes around its string values.

[[{"Name":Ashwin ,"Age":64}],[],[{"Name":Shakur ,"Age":64,"Gender":Male}]]

This is the valid version:

[[{"Name":"Ashwin" ,"Age":64}],[],[{"Name":"Shakur" ,"Age":64,"Gender":"Male"}]]

Here is how I prepare to extract the JSON data:

Sub Prep()
    Dim Data As Variant
    Data = Worksheets("sheet1").Cells(1, 1).Value
    
    Set Data = JsonConverter.ParseJson(Data)
        
    PrintJSONAccessors Data, "Data"

    Stop
End Sub

I put the Stop in the code so that I can test output in the Immediate Window.

Notice the data is a Dictionary inside a Collection inside another Collection.