Loop through child objects in json in VBA Macro

Question

I'm trying to loop through each item in the json file and get only few property values and put them in excel cells. Json file example-

{
  "123": {
     "fname":"Testing",
     "lname":"Lname",
     "age":"21",
     "email":"testing.lname@yopmail.com"
     },
  "234": {
     "fname":"...",
     "lname":"....",..........

it goes on like that.

I tried JsonConvertor.ParseJSon(content) and tried using MSScriptControl as well. With JsonConvert i was able to get only the first item value which is "123","234",.. but not their child. With MSScriptControl i was able to get the 123 as one object and 234 as another object and so on.. But using For Each item as in items statement throws error.

I also tried the code from the below thread, Excel VBA: Parsed JSON Object Loop

Still its showing "Object doesn't support this property or method" when i try to loop through the object which has the key and value.

Sub ImportJSON()
Dim Movie As Object
Dim scriptControl As Object
Dim fd As Office.FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)

With fd
    .Title = " Select a json file"
    .AllowMultiSelect = False

    If .Show() Then
        Filename = .SelectedItems(1)

        Dim content As String
        Dim iFile As Integer: iFile = FreeFile
        Open Filename For Input As #iFile
        content = Input(LOF(iFile), iFile)

'            Set scriptControl = CreateObject("MSScriptControl.ScriptControl")
'            scriptControl.Language = "JScript"
'            Set Movie = scriptControl.Eval("(" + content + ")")
'
'            For Each mov In Movie.Keys
'                For Each item In mov
'                Debug.Print mov("email")
'                Next
'            Next
'
'            MsgBox Movie.Email
'            'Debug.Print Movie.Email


        'Parse JSON String
        Dim items As Object
        Set items = JsonConverter.ParseJson(content)
        i = 1
        For Each item In items
            For Each Child In item
                Cells(i, 1) = item("email")
            Next Child
        Next item
    End If
End With

End Sub

I'm expecting to loop through each set of items (123,234,...) and get only few common fields like fname, lname and email. But i get error "object doesn't support this property or method"

Tim Williams · Accepted Answer · 2019-10-07T14:54:27.800

1

Something like this:

    Dim items As Object, itm, child
    Set items = JsonConverter.ParseJson(content)
    i = 1
    'loop over the "items" dictionary keys
    For Each itm In items
        Set child = items(itm) '<< get a reference to the nested dictionary
        Cells(i, 1) = itm
        Cells(i, 2) = child("email")
        Cells(i, 3) = child("age")
        'etc
    Next ite

edited Oct 07 '19 at 14:54

answered Oct 07 '19 at 03:30

Tim Williams

154,628
8
97
125

That worked. Thank you Tim. And i really forgot it is a object and i need to assign it to a object variable using Set. How dumb i'm.. Thanks anyways :) – Ram Oct 07 '19 at 23:33

Loop through child objects in json in VBA Macro

1 Answers1