I'm trying to write some VBA code in Excel to process and parse JSON files. My current configuration is, MacOS 14.7.2 (Sonoma), Excel 16.x. I was able to install the JsonConverter.bas into my project. To overcome the absence of "Microsoft Scripting" Reference for the JsonConverter.bas, I installed the VBA-Dictionary (). So the following code works until I hit the For Each loops at the end that iterate over the JsonConverter.ParseJson() Object with the error :
Run-time error '438' Object doesn't support this property or method
The initial JSON I'm using is pretty simple for now until I get the code working :
{
"TEST-CASE:successful-chart-consultation": {
"status": "403",
"statusText": "Forbidden",
"moreInformation": "Multiple client ids found."
}
}
In Debug mode I can see that the ParseJson has worked, but having never used JsonConverter before, I can't tell if it's labeled correctly. Structure wise, the Object looks correct though.
Does anyone know what the issue might be? I see this same parsing example in 20 other posts and it's working fine??
Here is the code : the Logger (log.log) is a silly Class I wrote to log stuff to a text file...can probably replace with Debug.Print. The ChooseFileMac() is a refactored version of Select_File_Or_Files_Mac and just returns a single selected *.json file in MacOS formatting...i.e. Macintosh HD:Users:n0002501:workspaces:ws-onegb:aws-cli:json3.json
Sub ImportJSONFile()
Dim jsonObject As Object
Dim ws As Worksheet
Dim i As Integer
Dim macPath As String
Dim jsonFile As String
Dim log As Logger ' Open a text file for logging output.
Set log = New Logger
log.Initialize ThisWorkbook.Path, "vba.log"
log.log "This is a test"
' Set the worksheet where you want to import data
Set ws = ThisWorkbook.Worksheets("Sheet1")
log.log "Workbook - [" & ThisWorkbook.Path & "]"
' Get the file path to your JSON file
macPath = doMacPath(ThisWorkbook.Path)
macPath = "Macintosh HD" & macPath & ":"
log.log "macPath - [" & macPath & "]"
jsonFile = ChooseFileMac(macPath)
log.log "jsonFile - [" & jsonFile & "]"
inputFile = FreeFile
Open jsonFile For Input As #inputFile
Dim jsonContent As String
Dim currentLine As String
Do While Not EOF(inputFile)
Line Input #inputFile, currentLine
log.log "currentLine - [" & currentLine & "]"
jsonContent = jsonContent & currentLine
Loop
log.log "jsonContent - [" & jsonContent & "]"
' Parse the JSON string using a JSON parsing library (like VBA-JSON)
Set jsonObject = JsonConverter.ParseJson(jsonContent)
Dim Item ' Tried as Object with no difference
' Write the parsed data to the worksheet (adjust loop based on your JSON structure)
i = 1
For Each Item In jsonObject
For Each Key In Item.Keys
ws.Cells(i, 1) = Key
ws.Cells(i, 2) = Item(Key)
i = i + 1
Next Key
Next Item
Set log = Nothing
End Sub
Tia, adym
I'm trying to write some VBA code in Excel to process and parse JSON files. My current configuration is, MacOS 14.7.2 (Sonoma), Excel 16.x. I was able to install the JsonConverter.bas into my project. To overcome the absence of "Microsoft Scripting" Reference for the JsonConverter.bas, I installed the VBA-Dictionary (https://github.com/VBA-tools/VBA-Dictionary). So the following code works until I hit the For Each loops at the end that iterate over the JsonConverter.ParseJson() Object with the error :
Run-time error '438' Object doesn't support this property or method
The initial JSON I'm using is pretty simple for now until I get the code working :
{
"TEST-CASE:successful-chart-consultation": {
"status": "403",
"statusText": "Forbidden",
"moreInformation": "Multiple client ids found."
}
}
In Debug mode I can see that the ParseJson has worked, but having never used JsonConverter before, I can't tell if it's labeled correctly. Structure wise, the Object looks correct though.
Does anyone know what the issue might be? I see this same parsing example in 20 other posts and it's working fine??
Here is the code : the Logger (log.log) is a silly Class I wrote to log stuff to a text file...can probably replace with Debug.Print. The ChooseFileMac() is a refactored version of Select_File_Or_Files_Mac and just returns a single selected *.json file in MacOS formatting...i.e. Macintosh HD:Users:n0002501:workspaces:ws-onegb:aws-cli:json3.json
Sub ImportJSONFile()
Dim jsonObject As Object
Dim ws As Worksheet
Dim i As Integer
Dim macPath As String
Dim jsonFile As String
Dim log As Logger ' Open a text file for logging output.
Set log = New Logger
log.Initialize ThisWorkbook.Path, "vba.log"
log.log "This is a test"
' Set the worksheet where you want to import data
Set ws = ThisWorkbook.Worksheets("Sheet1")
log.log "Workbook - [" & ThisWorkbook.Path & "]"
' Get the file path to your JSON file
macPath = doMacPath(ThisWorkbook.Path)
macPath = "Macintosh HD" & macPath & ":"
log.log "macPath - [" & macPath & "]"
jsonFile = ChooseFileMac(macPath)
log.log "jsonFile - [" & jsonFile & "]"
inputFile = FreeFile
Open jsonFile For Input As #inputFile
Dim jsonContent As String
Dim currentLine As String
Do While Not EOF(inputFile)
Line Input #inputFile, currentLine
log.log "currentLine - [" & currentLine & "]"
jsonContent = jsonContent & currentLine
Loop
log.log "jsonContent - [" & jsonContent & "]"
' Parse the JSON string using a JSON parsing library (like VBA-JSON)
Set jsonObject = JsonConverter.ParseJson(jsonContent)
Dim Item ' Tried as Object with no difference
' Write the parsed data to the worksheet (adjust loop based on your JSON structure)
i = 1
For Each Item In jsonObject
For Each Key In Item.Keys
ws.Cells(i, 1) = Key
ws.Cells(i, 2) = Item(Key)
i = i + 1
Next Key
Next Item
Set log = Nothing
End Sub
Tia, adym
Something like this (for your posted example json)
Function TestOutput()
Dim json As Object, txt, k, k2, i As Long, obj As Object, ws As Worksheet
Set ws = ActiveSheet ' for example
txt = ws.Range("A1").Value 'json is stored in a cell for testing
Set json = JsonConverter.ParseJson(txt) 'a Dictionary object
i = 5 'start row for output
For Each k In json.keys 'keys in top-level object (dict)
Set obj = json(k) 'nested dictionary
For Each k2 In obj.keys 'keys in nested object
ws.Cells(i, 1) = k 'top-level key
ws.Cells(i, 2) = k2 'inner key
ws.Cells(i, 3) = obj(k2) 'inner key value
i = i + 1
Next k2
Next k
End Function
Tim's answer should work, but I'll just add some context regarding why it works.
The class you are using (VBA-tools/VBA-Dictionary) is a remplacement for the Scripting Dictionary on Mac, but it doesn't support the For-Each syntax directly as discussed in this issue.
This means that you have to call .Keys or .Items on your VBA-Dictionary if you want to be able to use a For-Each loop.
Also note that this issue has been there since 2016 and is unlikely to be fixed anytime soon. If you want to use a dictionary substitute that supports For-Each directly and is currently still maintained, you can have a look at VBA-FastDictionary on GitHub.