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, Feb 13 '18 at 12:47
You already had an answer here - https://stackoverflow.com/questions/48703168/cannot-iterate-when-parsing-html-table-using-json-vba/48715323#48715323 — Tim Williams, Feb 13 '18 at 16:03
@TimWilliams I’m sorry my mistake, for some reason I didn’t see the new edits on your answer. — Leb_Broth, Feb 13 '18 at 17:42

score 2 · Accepted Answer · answered Feb 13 '18 at 12:49

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 '18 at 12:49

J.B.

445
1
4
8

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? – QHarr Feb 13 '18 at 13:01
1

Great question. Please parse the this json string on http://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. Feb 13 '18 at 13:12
Thanks for the explanation! – QHarr Feb 13 '18 at 13:13
1

Welcome @QHarr . I think this can be useful for you. https://codingislove.com/excel-json/ – J.B. Feb 13 '18 at 13:16

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

1 Answers1

Linked