getting error while parsing json file in excel vba

[Nihar1991]

Can anyone please help me on this. i have tried many methods but failed every time.

My json file looks like:

{"hMapCB":{

"AU":{
"listAdmins":[
{"UserName":"SelAuto1DN1","Password":"SelAuto1DN1"},
{"UserName":"SelAuto1DN1","Password":"SelAuto1DN1"},
{"UserName":"SelAuto1DN1","Password":"SelAuto1DN1"},
{"UserName":"SelAuto1DN1","Password":"SelAuto1DN1"},
{"UserName":"SelAuto1DN1","Password":"SelAuto1DN1"}]},

"CA":{
"listAdmins":[

{"UserName":"selauto1castg2@im.com","Password":"P@ssw0rd123"},
{"UserName":"selauto1castg2@im.com","Password":"P@ssw0rd123"},
{"UserName":"selauto1castg2@im.com","Password":"P@ssw0rd123"},
{"UserName":"selauto1castg2@im.com","Password":"P@ssw0rd123"},
{"UserName":"selauto1castg2@im.com","Password":"P@ssw0rd123"}]}
}}


And i need to read user name and password of type listAdmins and save it into the excel sheet where country name matches like (AU or CA).


Sample Code:


Sub ihdt()


Dim FSO As New FileSystemObject
Dim JsonTS As TextStream
Dim JsonText As String
Dim Parsed As Dictionary

' Read .json file
Set JsonTS = FSO.OpenTextFile("C:\Users\inarin01\Desktop\Macro learning\CredentialStage1_old.json", ForReading)
JsonText = JsonTS.ReadAll
JsonTS.Close

Set Parsed = JsonConverter.ParseJson(JsonText)

Set events = Parsed("hMapCB")

For Each k In events
MsgBox k
If k = "AU" Then
Set listAdmins = events(k)("listAdmins")
For Each v In listAdmins
MsgBox v
'Debug.Print , "participant", v
Next v
End If

Next

End Sub

 

[Kyle123]

Almost:

You want:

For Each k In events
    MsgBox k
    If k = "AU" Then
        Set listAdmins = events(k)("listAdmins")
        For Each v In listAdmins
            Debug.Print v("UserName"), v("Password")
        Next v
    End If
End Sub

Though this would be more efficient:

For Each k In Array("AU", "CA")
    Set listAdmins = events(k)("listAdmins")
    For Each v In listAdmins
        Debug.Print v("UserName"), v("Password")
    Next v
Next k