CODEWITHSUNDEEP
arraysjsonexcelvba
Ray Bond
Ray Bond

Reputation: 487

Access response in Json

I am using the JSON VBA library to parse a large JSON response but I can not access the elements of the response as expected.

For example the response has the structure:

{employees: [{employeeId: {id: 1234}, personNumber: "ABC123", shortName: "Bob",...},...],...}

I have tried :

Dim JsonPayload As Object
Set JsonPayload = JsonConverter.ParseJson(req.ResponseText)


MsgBox JsonPayload("dict_pDictionary")
MsgBox JsonPayload("employees")(1)("employeeId")

And also :

Set emp = JsonPayload("employees")
For Each e In emp
    Debug.Print "employee", e.shortName
    Debug.Print "shortName", e
    Debug.Print "shortName", e.dict_pDictionary
Next e

The error is Object Does not support this property or method.

I would like to know how to loop over the response. Some are variant/object/dictionary and other parts seem to be variant/object/collection

enter image description here

enter image description here

Upvotes: 0

Views: 86

Answers (1)

Tim Williams
Tim Williams

Reputation: 166401

VBA JSON converts objects ({}) to Dictionaries, and Arrays ([]) to Collections.

In your code e is a Dictionary object, not an object with named properties, so:

Dim jso As Object, emps, e

Set jso = JsonConverter.ParseJson( _
  "{""employees"":[{""employeeId"": {""id"": 1234}, ""personNumber"": ""ABC123"", ""shortName"": ""Bob""}]}")

Set emps = jso("employees")
For Each e In emps
    Debug.Print e("employeeId")("id")  '>> 1234
    Debug.Print e("shortName")         '>> Bob
Next e

Upvotes: 2

Related Questions