Excel VBA - How to get data from multiple-array JS

Question

I found a solution for parsing JSON, and it works fine for the presented example:

Here's the code:

    Sub Test()
    Dim jsonText As String
    Dim jsonObj As Dictionary
    Dim jsonRows As Collection
    Dim jsonRow As Collection
    Dim ws As Worksheet
    Dim currentRow As Long
    Dim startColumn As Long
    Dim i As Long

    Set ws = Worksheets("VIEW")

    'Create a real JSON object
    jsonText = ws.Range("A1").Value

    'Parse it
    Set jsonObj = JSON.parse(jsonText)

    'Get the rows collection
    Set jsonRows = jsonObj("rows")

    'Set the starting row where to put the values
    currentRow = 1

    'First column where to put the values
    startColumn = 2 'B

    'Loop through all the values received
    For Each jsonRow In jsonRows
        'Now loop through all the items in this row
        For i = 1 To jsonRow.Count
            ws.Cells(currentRow, startColumn + i - 1).Value = jsonRow(i)
        Next i

        'Increment the row to the next one
        currentRow = currentRow + 1
    Next jsonRow
End Sub

And the JSON that is working:

{"rows":[["20120604", "ABC", "89"],["20120604", "BCD", "120"],["20120604", "CDE","239"]]}

However I need to parse JSON that has a structure like this:

 [{"Id":"2604","Price": 520.4, "State": true},{"Id":"2605","Price": 322.8, "State": false},{"Id":"2619","Price": 104.7, "State": true},{"Id":"2628","Price": 182.2, "State": true}]

That means, in this case, It should be 3 columns (Id, Price, Status) and 4 rows.

It should be easy but I am just a total newbie here..

Answer 1

Should be something like this:

Dim jsonRows As Collection
Dim jsonRow As Dictionary 

'...

'Parse it
Set jsonRows = JSON.parse(jsonText)

'Set the starting row where to put the values
currentRow = 1

'First column where to put the values
startColumn = 2 'B

'Loop through all the values received
For Each jsonRow In jsonRows
    'Now set all the values in this row

    ws.Cells(currentRow, startColumn).Value = jsonRow("Id")
    ws.Cells(currentRow, startColumn + 1).Value = jsonRow("Price")
    ws.Cells(currentRow, startColumn + 2).Value = jsonRow("State")

    'Increment the row to the next one
    currentRow = currentRow + 1
Next jsonRow