Read json array and print the values using vba

Question

i have a json object that has a json array. I need to iterate array and print the values. I am using excel [vba].I am very new to VBA. Requesting anyone to help me out.

Set sr= CreateObject("MSScriptControl.ScriptControl") sr.Language = "JScript" Set Retval = MyScript.Eval("(" + newString + ")") MsgBox Retval.Earth.Fruits(0).name 

when i execute the above piece i am getting 'Object doesn't support this property or method'.

I need to iterate all the names under Fruit

Answer 1

I would use a json parser e.g. jsonconverter.bas as can use with 64bit and 32bit and doesn't represent the same security risk as scriptControl.

Jsonconverter.bas: Download raw code from here and add to standard module called jsonConverter . You then need to go VBE > Tools > References > Add reference to Microsoft Scripting Runtime.

Your json object is a dictionary with an inner dictionary Earth containing a collection Fruits (where Fruits is the key). The items in the collection are dictionaries with keys of "name" and values are the fruits. The [] denotes collection and {} dictionary.

Option Explicit Public Sub test() Dim s As String, json As Object, item As Object s = "{""Earth"":{""Fruits"":[{""name"":""Mango""},{""name"":""Apple""},{""name"":""Banana""}]}}" Set json = JsonConverter.ParseJson(s) For Each item In json("Earth")("Fruits") Debug.Print item("name") Next End Sub 

---

Example with regex:

Public Sub test() Dim s As String s = "{""Earth"":{""Fruits"":[{""name"":""Mango""},{""name"":""Apple""},{""name"":""Banana""}]}}" PrintMatches s End Sub Public Sub PrintMatches(ByVal s As String) Dim i As Long, matches As Object, re As Object Set re = CreateObject("VBScript.RegExp") With re .Global = True .MultiLine = True .IgnoreCase = False .Pattern = """name"":""(.*?)""" If .test(s) Then Set matches = .Execute(s) For i = 0 To matches.Count - 1 Debug.Print matches(i).SubMatches(0) Next i Else Debug.Print "No matches" End If End With End Sub