How can I parse the array objects inside this JSON list in VBA?

Question

My JSON is:

{"totalCount":431,"messages":[],"results":[{"aliasList":["User Id","Name","last name"],"results":[[71512,"joe","adams"],[23445,"jack","wilson"],[34566,jill,goodman]],"executionDate":151134568428}],"Class":"com.zoho.controlpanel.reports.ReportsItemVO"}

I want to parse the objects e.g. [71512,"joe","adams"] inside the second results key.

And this is my attempt to call the JSON-VBA parser:

Public Sub exceljson()
Dim http As Object, JSON As Object, i As Integer
Set http = CreateObject("MSXML2.XMLHTTP")
http.Open "GET", "http://controlpanel.zoho.verio/rest/reports/search/reports-call-center-my-assignments", False
http.send
Set JSON = ParseJson(ParseJson(http.responseText)("results"))("results")
Debug.Print JSON
i = 2
For Each Item In JSON
Sheets(5).Cells(i, 1).Value = Item("1")
Sheets(5).Cells(i, 2).Value = Item("2")
Sheets(5).Cells(i, 3).Value = Item("3")
i = i + 1
Next
MsgBox ("complete")
End Sub

I am getting error

run-time error 450 wrong number of arguments

What should I modify in my parser to properly parse these objects into a spreadsheet?

@QHarr yeah i know, it is an example for a JSON i have but a large dataset. the architecture is the same — Leb_Broth
– Leb_Broth, Commented Feb 13, 2018 at 12:47
You already had an answer here - stackoverflow.com/questions/48703168/… — Tim Williams
– Tim Williams, Commented Feb 13, 2018 at 16:03
@TimWilliams I’m sorry my mistake, for some reason I didn’t see the new edits on your answer. — Leb_Broth
– Leb_Broth, Commented Feb 13, 2018 at 17:42

J.B. · Accepted Answer · 2018-02-13 12:49:23Z

2

I have run the following code which is corrected and it is working fine. I was unable to open the URL but I have paste the jason string given by you in cell "P1". jill,goodman was manually covered in double quotes as it was wrong. I have commented certain part of code which you can use whenever it is required.

Public Sub exceljson()
Dim http As Object, JSON As Object, Item As Variant
Dim i As Integer

'Set http = CreateObject("MSXML2.XMLHTTP")
jsnStr = Range("P1")
'http.Open "GET", "http://controlpanel.zoho.verio/rest/reports/search/reports-call-center-my-assignments", False
'http.send
'Debug.Print http.responseText
'Set JSON = ParseJson(http.responseText)
Set JSON = ParseJson(jsnStr)

'Fetching data
i = 2
For Each Item In JSON("results")(1)("results")
    Sheets(2).Cells(i, 1).Value = Item(1)
    Sheets(2).Cells(i, 2).Value = Item(2)
    Sheets(2).Cells(i, 3).Value = Item(3)
    i = i + 1
Next
Set JSON = Nothing
Set http = Nothing

End Sub

answered Feb 13, 2018 at 12:49

J.B.

4451 gold badge4 silver badges8 bronze badges

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

3 Comments

QHarr Over a year ago

Do you mind explaining why it is JSON("results")(1)("results")? I can see that results is nested within results at index 1 but why is the end "results" needed please?

J.B. Over a year ago

Great question. Please parse the this json string on jsoneditoronline.org You will see square brackets and curly brackets after each name. square brackets represents it is an array while curly brackets represents it is an object. The data is saved in "results" >> "0" >> "results" node. The ("results")(1) specify the 1st index inside the first "results" array. Hence we are using JSON("results")(1)("results").

J.B. Over a year ago

Welcome @QHarr . I think this can be useful for you. codingislove.com/excel-json

Collectives™ on Stack Overflow

How can I parse the array objects inside this JSON list in VBA?

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related