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":"[email protected]" }, "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"
