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
– Tim Williams, Commented Feb 9, 2018 at 16:25
@TimWilliams I corrected the JSON and tried all the possible iterations without success — Leb_Broth
– Leb_Broth, Commented Feb 9, 2018 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
– Leb_Broth, Commented Feb 9, 2018 at 19:17
You can edit the Jason to obscure any confidential information — Tim Williams
– Tim Williams, Commented Feb 9, 2018 at 19:46

Tim Williams · Accepted Answer · 2018-02-12 17:36:15Z

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

Vityata · Accepted Answer · 2018-02-09 10:09:35Z

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 9, 2018 at 10:09

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

2 Comments

Leb_Broth Over a year ago

what im trying to do is to loop through all the totalCount users (=400).

Leb_Broth Over a year ago

im getting an error 9 subscript out of range when i changed data to object

Collectives™ on Stack Overflow

Cannot iterate when parsing HTML table using JSON-VBA

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related