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

my newString will hold : newString will hold : { "Earth": { "Fruits": [ { "name":"Mango" }, { "name":"Apple" }, ..... ] }, } — Danesh
– Danesh, Commented Aug 24, 2019 at 20:18

QHarr · Accepted Answer · 2019-08-25 06:25:18Z

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

edited Aug 25, 2019 at 6:25

answered Aug 24, 2019 at 20:58

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Danesh Over a year ago

Hi, thank you for the Swift reply.... As per my organization policy I am not allowed to use unapproved items. I came across same solution but I am not supposed to use that... Any other way.

QHarr Over a year ago

Are you aware of the risks of using scriptControl?! That aside... yes you could use regex or split.

QHarr Over a year ago

regex example added

Tim Williams Over a year ago

github.com/VBA-tools/VBA-JSON is just a code module you import into your project - no additional dll etc. If your company policy would prefer you to use a scriptcontrol over VBA-JSON then that's crazy. ScriptControl has potentially huge security issues.

AHeyne Over a year ago

@TimWilliams: Out of curiosity, what kind of security issues are known with ScriptControl? Do you have some links, maybe? Thanks in advance.

|

Collectives™ on Stack Overflow

Read json array and print the values using vba

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related