VBA- web辅助的VBA JSON解析

Question

我在VBA中有一些JSON解析，这让我发疯。下面是一个示例回复:

{
"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
}
}

穿越"data"很好，但是当我遍历属性时我得到了一个&;Type mismatch&;错误。这是我的代码。我没觉得有什么不对。任何想法吗?

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

任何人的帮助都是非常感激的!

Answer 1

下面两行是等价的…

For Each Nested In Item("attributes")

和

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

实际上，你在遍历字典中的每个键。因此，您的控制变量Nested被分配了一个字符串，因此类型不匹配。

由于Item("attributes")返回一个字典对象，您可以消除For Each/Next循环，并且您可以如下方式访问您的项…

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