• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Getting run-time error '0' while parsing json file in excel vba? can anyone help

Nihar Arisal

New Member
{"values":[
{"a":1,"b":2,"c": 3}]}

above json data is in a json file "testj.json"

Screen Shot 2018-12-20 at 13.32.42.png

code:
Code:
Sub Tester()
    Dim j, o, events, k, participants, v, sites

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


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

    Set j = JsonConverter.ParseJson(JsonText)

    Set events = j("hMapCB")

    For Each k In events
        Debug.Print "event", k
        stg = events(k)

        Set participants = events(k)("participants")
        For Each v In participants
            Debug.Print , "participant", v
        Next v

        Set sites = events(k)("sites")
        For Each v In sites
            Debug.Print , "site", v
        Next v

    Next

End Sub
upload_2019-1-8_16-59-40.png
 

Chihiro

Excel Ninja
You have more to the code than what you posted. Without that part can't really help you. As JsonConverter.ParseJson isn't native VBA function.

I'd recommend either uploading sample workbook with code, or posting entire code.
 

Nihar Arisal

New Member
Thanks Chihiro,

for your quick response. actually i am stuck with one assignment. my assignment is like to read data from json file and paste it into excel as per the criteria.

Can you 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).

upload_2019-1-8_20-42-50.png

I am getting below error while trying to read listadmins set.
upload_2019-1-8_20-47-0.png

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
 

Chihiro

Excel Ninja
Like I said. User using custom function "JsonConverter.ParseJson". That's not part of standard library. And I can't tell what it's doing or what's wrong with it, without looking at the code.

Which could be else where in the same module or in another module.

As I've asked, upload sample workbook with the code.
 

Chihiro

Excel Ninja
Since you have nested json format, you can't simply use the example given for VBA-JSON. That one is for single level json.

You'll need to traverse through Parsed() to retrieve values.

Ex:

Code:
Dim resColl As New Collection
For Each Key In Parsed("hMapCB").Keys
    For Each Key2 In Parsed("hMapCB")(Key).Keys
        For Each Key3 In Parsed("hMapCB")(Key)(Key2)
            resColl.Add Array(Key, Key3("UserName"), Key3("Password"))
        Next Key3
    Next
Next
There are 3 levels of dictionary and collections.

Parsed = Top level Dictionary
Parsed("hMapCB") = Holds 2 Dictionaries (Key = Counry)
Parsed("hMapCB")(Key) = Holds 1 dictionary each (Key = listAdmins)
Key3 is collection, with UserName & Password values.
 

Nihar Arisal

New Member
Hi chihiro,

Thanks for your help. i am able to iterate now and get the values. you are a champ buddy. thanks again :).

But now i am stuck after adding more values in that json file.
can you please help me on this. i am attaching my sample workbook and json file.

previously till listadmin key, i am able to fetch data. but when it comes to subordinate, it throws error.

actually i need to submit a project. please help me
 

Attachments

Chihiro

Excel Ninja
Your json isn't in correct format... That's why you have issue reading "SubOrdinate" to "Purchasing".
Check syntax of "listAdmins".

You need to follow same structure for the rest.

Ex:
Code:
"SubOrdinate":[{"UserName":"AUSubordinate1","Password":"PwddAdmin1"}],
"Basics":[{"UserName":"AUBasic1","Password":"PwddAdmin1"}],
"Support":[{"UserName":"AUSupport1","Password":"PwddAdmin1"}],
"Purchasing":[{"UserName":"AUAdmnin1","Password":"PwddAdmin1"}]},
Edit: Or if you can't change the json format... then you need to check TypeOf object before you process. If it's Scripting.Dictionary do one operation, if it's Collection then do another.
 
Last edited:

Chihiro

Excel Ninja
Here's sample code.
Code:
    For Each Key In Parsed("hMapCB").Keys
        For Each Key2 In Parsed("hMapCB")(Key).Keys
            If TypeOf Parsed("hMapCB")(Key)(Key2) Is Collection Then
                For Each Key3 In Parsed("hMapCB")(Key)(Key2)
                    Debug.Print Key3("UserName") & ", " & Key3("Password")
                Next
            Else
                Debug.Print Parsed("hMapCB")(Key)(Key2)("UserName") & ", " & Parsed("hMapCB")(Key)(Key2)("Password")
            End If
        Next
    Next
 
Last edited:
Top