JSON parser via VBA

[billgeo]

I downloaded the VB-JSON from JSON.org and have imported the "JSON.bas", "cJSONScript.cls" and "cStringBuilder.cls" to my project.
According to posts I've seen I also have added references to "Microsoft Scripting Runtime" and to "Microsoft ActiveX Data Objects 2.8 Library".
I also created the below "Test" macro from another poster to run my test.

I then copied my JSON data into A1 as that is were this macro looks for the JSON to parse.
However, I can not seem to get past an "Object required" error on the line with;

Set jsonRows = jsonObj("rows")

I'm running Excel 2007 and VBA 6.5.
Any insights would be VERY appreciated.
FYI: I'm completely new to JSON and relatively new to VBA.

Thanks
Bill


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("Sheet1")

'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") <---- errors here

'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

 

[John_w]

Do you still need help with this? It's difficult to help without seeing the JSON string, but try this sort of syntax:

MsgBox JSONobj.item("rows").item("xyz")            
MsgBox JSONobj.item("rows").item(1).item("xyz")

Here is a recursive procedure and supporting test routine which writes the object produced by the VB-JSON parse function to Excel cells, to help understand the structure of the data. You could also use an online JSON viewer such as Online JSON Viewer.

Add this code to the JSON VBA module (the one which contains the parse function "Public Function parse(ByRef str As String) As Object") :

'Output parsed JSON object to Excel cells
Public Function toCells(ByRef obj As Variant, destCell As Range) As Long
    Dim n As Long
    
    n = 1 'default number of rows written by current call
    
    Select Case VarType(obj)
        
        Case vbNull
            destCell.value = "null"
        
        Case vbDate
            destCell.value = CStr(obj)
        
        Case vbString
            destCell.value = obj 'Encode(obj)
            
        Case vbObject
        
            If TypeName(obj) = "Dictionary" Then
            
                Dim i As Long
                Dim keys, key
                keys = obj.keys
                n = 0
                For i = 0 To obj.Count - 1
                    key = keys(i)
                    destCell.Offset(n, 0).value = key
                    n = n + toCells(obj.item(key), destCell.Offset(n, 1))
                Next i
                Set destCell = destCell.Offset(n, 0)
                   
            ElseIf TypeName(obj) = "Collection" Then
            
                Dim colValue
                i = 0
                n = 1
                For Each colValue In obj
                    destCell.Offset(n, 0).value = i
                    n = n + toCells(colValue, destCell.Offset(n, 1))
                    i = i + 1
                Next colValue
                Set destCell = destCell.Offset(n, 0)
                
            End If
            
        Case vbBoolean
            If obj Then destCell.value = "true" Else destCell.value = "false"
            
        Case vbVariant, vbArray, vbArray + vbVariant
            Dim sEB
            'UNTESTED
            Stop
            destCell.value = multiArray(obj, 1, "", sEB)
            
        Case Else
            destCell.value = Replace(obj, ",", ".")
            
    End Select

    toCells = n
    
End Function

Here is the test routine which you should paste into a separate module:

Public Sub TestJson2()

    Dim JSONstring As String
    Dim JSONobj As Object
    Dim lr As Long, r As Long
    
    'Load JSON string from text file
    'With CreateObject("Scripting.FileSystemObject")
    '    JsonString = .OpenTextFile("C:\path\to\json.txt", 1).ReadAll
    'End With
    
    'Load JSON string from Sheet1 cells A1 to last row in column A
    
    With Worksheets("Sheet1")
        lr = .Cells(rows.Count, "A").End(xlUp).row
        JSONstring = ""
        For r = 1 To lr
            JSONstring = JSONstring & .Cells(r, "A").value
        Next
    End With
    
    Set JSONobj = JSON.parse(JSONstring)
    
    With Worksheets("Sheet2")
        .Cells.ClearContents
        JSON.toCells JSONobj, .Range("A1")
    End With
    
End Sub

The test routine loads the JSON string from Sheet1 column A starting at A1 (there is also commented-out code which loads the JSON from a text file) and then calls the JSON.toCells function to write the JSON structure to Sheet2 starting at cell A1.

 

[billgeo]

Thanks John!!!
I'll check this out hopefully tomorrow and let you know!!!

 

[billgeo]

John

That seems to work! Thanks. I'll look over what you've done to try to understand it.
Thanks again!

Bill

 

[sifar786]

Thanks John,

This is the logic i was searching for on the internet.

Do you still need help with this? It's difficult to help without seeing the JSON string, but try this sort of syntax:

MsgBox JSONobj.item("rows").item("xyz")            
MsgBox JSONobj.item("rows").item(1).item("xyz")

Here is a recursive procedure and supporting test routine which writes the object produced by the VB-JSON parse function to Excel cells, to help understand the structure of the data. You could also use an online JSON viewer such as Online JSON Viewer.

Add this code to the JSON VBA module (the one which contains the parse function "Public Function parse(ByRef str As String) As Object") :

'Output parsed JSON object to Excel cells
Public Function toCells(ByRef obj As Variant, destCell As Range) As Long
    Dim n As Long
    
    n = 1 'default number of rows written by current call
    
    Select Case VarType(obj)
        
        Case vbNull
            destCell.value = "null"
        
        Case vbDate
            destCell.value = CStr(obj)
        
        Case vbString
            destCell.value = obj 'Encode(obj)
            
        Case vbObject
        
            If TypeName(obj) = "Dictionary" Then
            
                Dim i As Long
                Dim keys, key
                keys = obj.keys
                n = 0
                For i = 0 To obj.Count - 1
                    key = keys(i)
                    destCell.Offset(n, 0).value = key
                    n = n + toCells(obj.item(key), destCell.Offset(n, 1))
                Next i
                Set destCell = destCell.Offset(n, 0)
                   
            ElseIf TypeName(obj) = "Collection" Then
            
                Dim colValue
                i = 0
                n = 1
                For Each colValue In obj
                    destCell.Offset(n, 0).value = i
                    n = n + toCells(colValue, destCell.Offset(n, 1))
                    i = i + 1
                Next colValue
                Set destCell = destCell.Offset(n, 0)
                
            End If
            
        Case vbBoolean
            If obj Then destCell.value = "true" Else destCell.value = "false"
            
        Case vbVariant, vbArray, vbArray + vbVariant
            Dim sEB
            'UNTESTED
            Stop
            destCell.value = multiArray(obj, 1, "", sEB)
            
        Case Else
            destCell.value = Replace(obj, ",", ".")
            
    End Select

    toCells = n
    
End Function

Here is the test routine which you should paste into a separate module:

Public Sub TestJson2()

    Dim JSONstring As String
    Dim JSONobj As Object
    Dim lr As Long, r As Long
    
    'Load JSON string from text file
    'With CreateObject("Scripting.FileSystemObject")
    '    JsonString = .OpenTextFile("C:\path\to\json.txt", 1).ReadAll
    'End With
    
    'Load JSON string from Sheet1 cells A1 to last row in column A
    
    With Worksheets("Sheet1")
        lr = .Cells(rows.Count, "A").End(xlUp).row
        JSONstring = ""
        For r = 1 To lr
            JSONstring = JSONstring & .Cells(r, "A").value
        Next
    End With
    
    Set JSONobj = JSON.parse(JSONstring)
    
    With Worksheets("Sheet2")
        .Cells.ClearContents
        JSON.toCells JSONobj, .Range("A1")
    End With
    
End Sub

The test routine loads the JSON string from Sheet1 column A starting at A1 (there is also commented-out code which loads the JSON from a text file) and then calls the JSON.toCells function to write the JSON structure to Sheet2 starting at cell A1.