Cannot iterate when parsing HTML table using JSON-VBA

Question

This is an example in JSON view of my users table that i want to parse", inluding an aliasList as the title for each column in the HTML table and the remaining users are below it.

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

And this my parse caller that is getting a Compile error:

For Each may only iterate over a collection object or an array

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/NONE&offset=0", False
http.send
Set JSON = ParseJson(http.responseText)
i = 2
Dim data As String
data = JSON("results")(2)
Dim parsedData As Dictionary
Set parsedData = ParseJson(data)
For Each item In data
Sheets(5).Cells(i, 1).Value = item("results")(2) 'key is called simply "2"
Sheets(5).Cells(i, 2).Value = item("results")(4)
Sheets(5).Cells(i, 3).Value = item("results")(6)
Sheets(5).Cells(i, 4).Value = item("results")(13)
Sheets(5).Cells(i, 5).Value = item("results")(16)
Sheets(5).Cells(i, 6).Value = item("results")(18)
i = i + 1
Next
MsgBox ("complete")
End Sub

I tried many combinations in the Data declaration and which nested array to parse without success. However, i don't know if i need to use the aliasList in this case, which i don't know how to do it.

What should i modify in my call?

Would be more helpful to post a sample of the actual JSON. Vityata is right though - you aren't treating `data` as an object - at least add a Set there when you assign it. — Tim Williams, Feb 09 '18 at 16:25
@TimWilliams I corrected the JSON and tried all the possible iterations without success — Leb_Broth, Feb 09 '18 at 18:45
@TimWilliams I tried my best but unfortunately they are intranet material and can’t have them as info on SO. However with the previous architecture it was the exact view of the json itself. — Leb_Broth, Feb 09 '18 at 19:17
You can edit the Jason to obscure any confidential information — Tim Williams, Feb 09 '18 at 19:46
@TimWilliams i updated the architecture to be parseable. I hope this will help. — Leb_Broth, Feb 09 '18 at 20:16
You can't [usefully] have two keys both called "results": https://stackoverflow.com/questions/5306741/do-json-keys-need-to-be-unique — Tim Williams, Feb 09 '18 at 21:44
@TimWilliams hello again Tim, i updated my JSON as it is in our system. I masked of course some info but didnt modify anything in the architecture; still though, the "results" key inside a primary "results" key. — Leb_Broth, Feb 12 '18 at 09:01

score 1 · Answer 1 · answered Feb 09 '18 at 10:09

1

This part of your code says that data is a String:

Dim data As String
data = JSON("results")(2)

Thus, it is not iterable. Try to make data something else and to iterate through it. This is an example of a way to iterate through a string:

Option Explicit

Public Sub TestMe()

    Dim data            As String
    Dim somethingElse   As Variant

    data = "How much should I party today?"
    somethingElse = Split(data)

    Dim cnt             As Long
    For cnt = LBound(somethingElse) To UBound(somethingElse)
        Debug.Print somethingElse(cnt)
    Next cnt

End Sub

answered Feb 09 '18 at 10:09

Vityata

42,633
8
55
100

what im trying to do is to loop through all the totalCount users (=400). – Leb_Broth Feb 09 '18 at 10:13
im getting an error 9 subscript out of range when i changed data to object – Leb_Broth Feb 09 '18 at 10:16

Tim Williams · Accepted Answer · 2018-02-12T17:36:15.293

Your JSON is malformed, since you can only have one instance of each unique key. When the second "results" is parsed it just overwrites the first.

This worked for me (after fixing the errors in your JSON):

Dim j, k, o

'loading json from worksheet cell...
Set j = JsonConverter.ParseJson(Sheet1.Range("B6").Value)

For Each o In j("results")
    Debug.Print o("1"), o("2"), o("3")
Next

EDIT: updated for your "actual" json:

Sub Tester55()

    Dim j, c, res, v

    'loading json from worksheet cell...
    Set j = JsonConverter.ParseJson(Sheet1.Range("B6").Value)
    Set res = j("results")

    Set c = ActiveSheet.Range("F2")

    WriteCollection c, res(1)("aliasList")

    For Each v In res(1)("results")
        Set c = c.Offset(1, 0)
        WriteCollection c, v
    Next v

End Sub

Sub WriteCollection(rng, col)
    Dim v, i As Long
    For Each v In col
        rng.Offset(0, i).Value = v
        i = i + 1
    Next v
End Sub

Cannot iterate when parsing HTML table using JSON-VBA

2 Answers2

Linked