how to display my JSON object in good format in VBA Excel

I know display my Parse Json in cell Excel when the Json is "simple" (when it's just string inside) but now i have String, object and array and i'm a little lost..
my json is following:

[
{
"name": null,
"type": null,
"actions": ,
"screen": null,
"container": null,
"sysid": 5,
"uftitem": null
},
{
"name": null,
"type": null,
"actions": ,
"screen": null,
"container": null,
"sysid": 6,
"uftitem": null
},
{
"name": "UTProject5",
"type": "type",
"actions": [
{
"name": "UTProject",
"description": "UTProject",
"pattern": "UTProject",
"isCheck": true,
"sysid": 1,
"uftaction": {
"sysid_uftAction": 2,
"code": "code uft",
"maxTime": 10,
"nbCycle": 20
}
},
{
"name": "UTProject2",
"description": "UTProject",
"pattern": "UTProject",
"isCheck": true,
"sysid": 3,
"uftaction": {
"sysid_uftAction": 4,
"code": "code uft",
"maxTime": 10,
"nbCycle": 20
}
}
],
"screen": {
"name": null,
"type": null,
"actions": ,
"screen": null,
"container": null,
"sysid": 5,
"uftitem": null
},
"container": {
"name": null,
"type": null,
"actions": ,
"screen": null,
"container": null,
"sysid": 6,
"uftitem": null
},
"sysid": 7,
"uftitem": {
"code": "code",
"parentCode": "tooooz",
"sysid": 8
}
},
{
"name": "UTProject6",
"type": "type",
"actions": [
{
"name": "UTProject",
"description": "UTProject",
"pattern": "UTProject",
"isCheck": true,
"sysid": 1,
"uftaction": {
"sysid_uftAction": 2,
"code": "code uft",
"maxTime": 10,
"nbCycle": 20
}
},
{
"name": "UTProject2",
"description": "UTProject",
"pattern": "UTProject",
"isCheck": true,
"sysid": 3,
"uftaction": {
"sysid_uftAction": 4,
"code": "code uft",
"maxTime": 10,
"nbCycle": 20
}
}
],
"screen": {
"name": null,
"type": null,
"actions": ,
"screen": null,
"container": null,
"sysid": 5,
"uftitem": null
},
"container": {
"name": null,
"type": null,
"actions": ,
"screen": null,
"container": null,
"sysid": 6,
"uftitem": null
},
"sysid": 9,
"uftitem": {
"code": null,
"parentCode": null,
"sysid": 10
}
}
]


I would like to access to what I want and display it in cell, but I don't know access in array and object.

Thanks every one!

1 Answer
1

General:

You can empty the whole thing with the following code which makes use of JSON converter:

Note:

I am reading JSON string in from sheet and storing in object via JSONConverter. The initial object is a collection. I loop that collection and every nested level within using TypeName function* to determine what object(s) are stored at each level. I then use Select Case to handle these objects appropriately.

JSONConverter

TypeName

Select Case

More efficient would be to design a re-usuable class to handle this. I have seen some other questions on SO where this is done.

_{* VarType is actually more robust}

Example JSON

Example code output to immediate window:

You can choose how you write to the cell by replacing the Debug.Print statements with assignments to sheet ranges.

Debug.Print

VBA:

Option Explicit
Public Sub GetInfoFromSheet()
Dim jsonStr As String
jsonStr = [A1] '<== read in from sheet
Dim json As Object
Set json = JsonConverter.ParseJson(jsonStr)

Dim i As Long, j As Long, key As Variant
For i = 1 To json.Count
For Each key In json(i).keys
Select Case key
Case "name", "type"
Debug.Print key & " " & json(i)(key)
Case Else
Select Case TypeName(json(i)(key))
Case "Dictionary"
Dim key2 As Variant
For Each key2 In json(i)(key)
Select Case TypeName(json(i)(key)(key2))
Case "Collection"
Dim k As Long
For k = 1 To json(i)(key)(key2).Count
Debug.Print key & " " & key2 & " " & json(i)(key)(key2)(k)
Next k
Case Else
Debug.Print key & " " & key2 & " " & json(i)(key)(key2)
End Select
Next key2
Case "Collection"
For j = 1 To json(i)(key).Count '<== "actions"
Dim key3 As Variant
For Each key3 In json(i)(key)(j).keys
Select Case TypeName(json(i)(key)(j)(key3))
Case "String", "Boolean", "Double"
Debug.Print key & " " & key3 & " " & json(i)(key)(j)(key3)
Case Else
Dim key4 As Variant
For Each key4 In json(i)(key)(j)(key3).keys
Debug.Print key & " " & key3 & " " & key4 & " " & json(i)(key)(j)(key3)(key4)
Next key4
End Select
Next key3
Next j
Case Else
Debug.Print key & " " & json(i)(key)
End Select
End Select
Next key
Next i
End Sub


tl;dr; Tutorial spot:

So the above might have been a bit full on as it gets everything without lots of explanation. Below, we take a more detailed look at how to target some of that JSON and "talk" through the associated VBA.

For this you can use an online JSON parser to view the structure of your JSON more clearly. I posted your JSON string into Json Parser Online, and then examined the structure in the String/parseJS eval; left hand side section. There are other tools available.

JSON

JSON

String/parseJS eval

The initial thing to note is the beginning "[". The very first one you can see below.

"["

This denotes the Collection object, which is your JSON string when converted with JsonConverter. Everything else is nested between this opening "[" bracket and its closing counterpart at the very end.

Collection

JSON

JsonConverter

"["

The next thing to note is that this is a collection of dictionaries, so everything that forms a "group" within, is a dictionary.

See the "{" denoting the start of the dictionary?

"{"

The dictionary has keys of "name","type","actions" etc.

"name","type","actions"

An initial observation is that lots of this info is empty ie. null.
We can ignore these with an IsNull test (I choose to do this based on "name" field):

null

IsNull

"name"

If Not IsNull(json(i)("name"))


We can also see that "actions", in dictionaries where the "name" is not null, contains another collection of dictionaries. You see we have the "[" followed by the "{" as described before.

"actions"

"name"

null

"["

"{"

We can see that each inner dictionary has keys of "name", "description" etc. We can also see that their values are of different datatypes.

"name", "description"

Observing "actions" in the JSON structure, you can see these are (using an example dictionary):

"actions"

JSON

"name":"UTProject"

"description":"UTProject"

pattern":"UTProject"

"isCheck":true

"sysid":1

"uftaction" 'keys of ==> "sysid_uftAction":2,"code":"code uft","maxTime":10,"nbCycle":20

So, we can use Select Case to handle the data type by testing with TypeName

Select Case

TypeName

For the primitive boolean, string and double data types we can simply print them by using the key e.g.

json(i)("actions")(j)("isCheck")


That will be a boolean result of True or False. i and j being indices of current position in loops of both outer and inner collections.

True

False.

i

j

For the dictionary "uftaction", we can loop over its keys:

"uftaction"

For Each key2 In json(i)("actions")(j)(key).keys
Debug.Print "actions " & key & " " & key2 & " " & json(i)("actions")(j)(key)(key2)
Next key2


You could of course access with the name of the key without the loop over the keys at the end, e.g.:

json(i)("actions")(j)(key)("maxTime")


And throughout you could access specific positions via index rather than looping such that i and j would be replaced directly with a numeric value. And key, key2 etc could be replaced by the actual literal string for any given key.

i

j

key, key2

Hopefully that has given you some more insight.

VBA:

Option Explicit
Public Sub GetInfoFromJSON()
Dim jsonStr As String
jsonStr = [A1] '<== read in from sheet
Dim json As Object, i As Long
Set json = JsonConverter.ParseJson(jsonStr) '<==This is a collection verified by Debug.Print TypeName(JSON)
For i = 1 To json.Count
If Not IsNull(json(i)("name")) Then
'ignore the null names which have sys id only populated
Debug.Print "name" & " " & json(i)("name")
Debug.Print "type" & " " & json(i)("type")
Dim j As Long
For j = 1 To json(i)("actions").Count 'actions are a collection of dictionaries
Dim key As Variant
For Each key In json(i)("actions")(j).keys 'dictionary
'observing actions in the JSON structure you can see there are:
' String "name":"UTProject"
'String "description":"UTProject",
'String "pattern":"UTProject",
'Boolean "isCheck":true,
'Double "sysid":1,
'Dictionary "uftaction" '==> "sysid_uftAction":2,"code":"code uft","maxTime":10,"nbCycle":20
'So we can use Select Case to handle the data type by testing with TypeName
Select Case TypeName(json(i)("actions")(j)(key))
Case "String", "Boolean", "Double" '<==good to go nothing extra needed
Debug.Print "actions " & key & " " & json(i)("actions")(j)(key)
Case Else ' we are dealing with uftaction which we know is a dictionary
Dim key2 As Variant
For Each key2 In json(i)("actions")(j)(key).keys '<==We know now we are looping the uftaction dictionary which has keys "sysid_uftAction","code","maxTime","nbCycle"
Debug.Print "actions " & key & " " & key2 & " " & json(i)("actions")(j)(key)(key2)
Next key2
End Select
Next key
Next j
End If
Next i
End Sub


Nice post. very thorough, and VBA+JSON has such limited resources, this something like this will be useful to many
– ashleedawg
2 days ago

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.