I have been using JsonConverter
to parse JSON
file and then using following code to extract all the "label" and sub "values" from dictionary
into my userform combobox
.
Set JSP = JsonConverter.ParseJson(JSONtxtString)
For Each A In JSP
Debug.Print A
If Not IsObject(JSP(A)) Then
Debug.Print JSP(A)
Else
For Each B In JSP(A)
If Not IsObject(JSP(B)) Then
'Debug.Print B("label")
Me.AttributeComBo.AddItem B("label")
Me.ConditionBox.AddItem B("label")
If B("type") = "selectboxes" Or B("type") = "select" Then
For Each C In B("values")
'Debug.Print C("label")
Me.CBSubValues.AddItem C("label")
Next C
End If
Else
End If
Next B
End If
Next A
however I could not get the values("label") from "Favorate color". from the following JSON.
{"components": [
{
"label": "Family Name",
"tableView": true,
"key": "familyName",
"type": "textfield",
"input": true
},
{
"label": "Amount of Money",
"mask": false,
"tableView": false,
"delimiter": false,
"requireDecimal": false,
"inputFormat": "plain",
"truncateMultipleSpaces": false,
"key": "amountOfMoney",
"type": "number",
"input": true
},
{
"label": "I hereby confirm",
"tableView": false,
"key": "iHerebyConfirm",
"type": "checkbox",
"input": true,
"defaultValue": false
},
{
"label": "Which Cities do you like",
"optionsLabelPosition": "right",
"tableView": false,
"values": [
{
"label": "New York",
"value": "newNew YorkYork",
"shortcut": ""
},
{
"label": "Munich",
"value": "Munich",
"shortcut": ""
},
{
"label": "Paris",
"value": "Paris",
"shortcut": ""
},
{
"label": "Hongkong",
"value": "Hongkong",
"shortcut": ""
},
{
"label": "Mumbai",
"value": "Mumbai",
"shortcut": ""
}
],
"key": "whichCitiesDoYouLike",
"type": "selectboxes",
"input": true,
"inputType": "checkbox"
},
{
"label": "Favorite color",
"widget": "choicesjs",
"tableView": true,
"data": {
"values": [
{
"label": "black",
"value": "black"
},
{
"label": "white",
"value": "white"
},
{
"label": "blue",
"value": "blue"
},
{
"label": "green",
"value": "green"
}
]
},
"key": "favoriteColor",
"type": "select",
"input": true
},
{
"type": "button",
"label": "Submit",
"key": "submit",
"disableOnInvalid": true,
"input": true,
"tableView": false
}
]
}
I could not get the values i.e black, white, blue, green from label("Favorite color") WHAT IS THE BETTER SOLUTION TO EXTRACT ALL THE VALUES FROM ANY JSON FILE? how can I loop through each object in JSON and extract all the values?
CodePudding user response:
Working with JSON in VBA can be frustrating. I created this answer that I use as my own reference. But more importantly, take the time to clearly identify what parts of the JSON are being referenced.
In your case, the top level is a Collection
of components
. Each component has a label
. A component may have an array of values
.
In the case of the "Favorite Color" component though, the values
are inside a data
Dictionary
.
Here is some example code I use to keep it all clear and straight:
Option Explicit
Sub test2(ByRef jsonText As String)
Dim json As Variant
Set json = JsonConverter.ParseJson(jsonText)
Dim components As Collection
Set components = json("components")
Dim component As Variant
For Each component In components
Dim label As String
label = component("label")
Debug.Print "Label: " & label
On Error Resume Next
Dim values As Collection
Set values = component("values")
Dim data As Dictionary
Set data = component("data")
On Error GoTo 0
Dim value As Variant
If Not values Is Nothing Then
For Each value In values
Debug.Print " Value: " & value("label")
Next value
ElseIf Not data Is Nothing Then
Set values = data("values")
For Each value In values
Debug.Print " Value: " & value("label")
Next value
Else
Debug.Print " No values"
End If
Set values = Nothing
Next component
End Sub
Note that the values
are not guaranteed to exist in each component
. That's why we have to disable/enable error checking.