1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

  3. 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

Discussion in 'VBA Macros' started by Nihar Arisal, Jan 8, 2019.

  1. Nihar Arisal

    Nihar Arisal New Member

    Messages:
    5
    {"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 (vb):

    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
  2. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,171
    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.
  3. Nihar Arisal

    Nihar Arisal New Member

    Messages:
    5
    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
  4. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,171
    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.
  5. Nihar Arisal

    Nihar Arisal New Member

    Messages:
    5
    Hi chihiro,

    please find the attached sample workbook.

    Attached Files:

  6. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,171
    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 (vb):
    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.
  7. Nihar Arisal

    Nihar Arisal New Member

    Messages:
    5
    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

    Attached Files:

  8. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,171
    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 (vb):
    "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: Jan 9, 2019
  9. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,171
    Here's sample code.
    Code (vb):
        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: Jan 9, 2019
  10. Nihar Arisal

    Nihar Arisal New Member

    Messages:
    5
    Thanks a lot Chihiro,

    it works.

    thanks again for putting your valuable time to help me.

Share This Page