Access Elements from JSON

Question

I am trying to access "id", "first_name" from the json in Excel vba.

{
    "paging": {
        "per_page": 10,
        "page": 1,
        "previous": null,
        "self": "/api/v1/users?per_page=10&page=1",
        "next": null
    },
    "data": [
        {
            "id": 15093,
            "first_name": "ala",
            "last_name": "adasd",
            "display_name": "Ali Saigar",
            "email": "aadaasdr@yahoo.co.uk",
            "user_type_id": 1,
            "billable": true,
            "hire_date": null,
            "termination_date": null,
            "mobile_phone": null,
            "office_phone": null,
            "deleted": false,
            "deleted_at": null,
            "account_owner": true,
            "invitation_pending": false,
            "user_settings": 786568,
            "guid": "c010323e-bf44-4ed1-ac70-85858475df14",
            "employee_number": null,
            "billability_target": 100,
            "billrate": -1,
            "role": null,
            "discipline": null,
            "location": null,
            "has_login": true,
            "thumbnail": ""
        },
        {
            "id": 15094,
            "first_name": "James",
            "last_name": "Bond",
            "display_name": "James Bond",
            "email": null,
            "user_type_id": 3,
            "billable": true,
            "hire_date": null,
            "termination_date": null,
            "mobile_phone": null,
            "office_phone": null,
            "deleted": false,
            "deleted_at": null,
            "account_owner": false,
            "invitation_pending": false,
            "user_settings": 0,
            "guid": "91e6a979-1753-46a0-bae3-37cf4003e953",
            "employee_number": null,
            "billability_target": 100,
            "billrate": -1,
            "role": null,
            "discipline": null,
            "location": null,
            "has_login": false,
            "thumbnail": ""
        }
    ]
}

Can any body tell me how to access it from vba? I tried many methods, but not able to do it successful,

possible duplicate of [Is There a JSON Parser for VB6 / VBA?](http://stackoverflow.com/questions/2782076/is-there-a-json-parser-for-vb6-vba) — Marc, May 16 '15 at 20:19
I am using the following https://github.com/VBA-tools/VBA-JSON — Mithun Manohar, May 16 '15 at 20:20
Set Json = JsonConverter.ParseJson(response) Status = Json("data")("id")(0) — Mithun Manohar, May 16 '15 at 20:23
I am a newbie and not able to figure it out. The documentation at github.com/VBA-tools/VBA-JSON don't have example of such cases. — Mithun Manohar, May 16 '15 at 20:25

score 1 · Answer 1 · answered May 16 '15 at 20:50

Alternatively:

Sub JsonTest()

    Dim json As String
    Dim sc As Object
    Dim o

    Set sc = CreateObject("scriptcontrol")
    sc.Language = "JScript"

    json = Range("A1").Value 'for my testing...

    sc.eval "var obj=(" & json & ")" 'evaluate the json

    Debug.Print sc.eval("obj.data.length")
    Debug.Print sc.eval("obj.data[0].id")
    Debug.Print sc.eval("obj.data[0].email")
    Debug.Print sc.eval("obj.data[0].guid")

End Sub

Access Elements from JSON

1 Answers1