VBA JSON Parsing with VBA-Web Assistance

Question

I have some JSON parsing in VBA that's driving me nuts. Here's a sample response:

{
    "data": [
        {
            "type": "access_user",
            "attributes": {
                "name": "Me",
                "email": null,
                "phone": null,
                "department": "",
                "status": "current",
                "source": null,
                "guest_source": null,
                "created_at": "2019-08-19T21:45:56Z",
                "updated_at": "2019-08-22T03:38:33Z",
                "pin": "77005",
                "card_number": null
            },
            "id": "be66e054-5509-4cf6-a5c2-14b85eb25619",
            "links": {
                "self": "https://api"
            }
        },
        {
            "type": "access_user",
            "attributes": {
                "name": "Day Code",
                "email": null,
                "phone": null,
                "department": null,
                "status": "current",
                "source": null,
                "guest_source": null,
                "created_at": "2019-08-21T19:49:29Z",
                "updated_at": "2021-06-16T16:08:28Z",
                "pin": "12345",
                "card_number": null
            },
            "id": "3a615a3d-eb1c-4973-9e9f-ace5e29ca964",
            "links": {
                "self": "https://api"
            }
        }
    ],
    "meta": {
        "page": 1,
        "per_page": 25,
        "total_count": 2,
        "total_pages": 1
    }
}

Traversing "data" is fine, but when I traverse "attributes" I'm getting a "Type mismatch" error. Here's my code. I'm not seeing anything wrong. Any thoughts?

For Each Item In Response2.Data("data")
    UserType = Item("type")
    Id = Item("id")
    If UserType = "access_user" Then
        For Each Nested In Item("attributes")
            Name = Nested("name")
            status = Nested("status")
            PIN = Nested("pin")
        Next
    End If
Next

Anyone's assistance is greatly appreciated!

user6585761 · Accepted Answer · 2022-05-20 20:16:57Z

1

The following two lines are equivalent . . .

For Each Nested In Item("attributes")

and

For Each Nested In Item("attributes").Keys()

So, in fact, you're looping through each key within the dictionary. And so your control variable Nested is assigned a string, hence the type mismatch.

Since Item("attributes") returns a dictionary object, you can eliminate the For Each/Next loop, and you can access your items as follows . . .

    Name = Item("attributes")("name")
    Status = Item("attributes")("status")
    PIN = Item("attributes")("pin")

answered May 20, 2022 at 20:16

user6585761

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA JSON Parsing with VBA-Web Assistance

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related