While importing JSON files to an Excel Table is easy using a Get and Transform query, going the other way and saving an Excel table as a JSON file is unfortunately not as simple. Luckily, with the code below, it’s easy to save the first Excel table in your sheet as a JSON file.
Step 1: Setup VBA-JSON and Add References
First, download VBA-JSON. Follow the instructions on the GitHub page to install. In essence, use File > Import File to import. Ensure you have a reference to Microsoft Scripting Runtime as shown below.
Step 2: The code
Private Sub SaveAsJson_Click()
Set objectProperties = CreateObject("Scripting.Dictionary")
For Each c In ActiveSheet.ListObjects(1).HeaderRowRange.Cells
objectProperties.Add c.Column, c.Value
Next
Dim collectionToJson As New Collection
For Each r In ActiveSheet.ListObjects(1).ListRows
Set jsonObject = CreateObject("Scripting.Dictionary")
For Each c In r.Range.Cells
jsonObject.Add objectProperties(c.Column), c.Value
Next
collectionToJson.Add jsonObject
Next
fileSaveName = Application.GetSaveAsFilename(fileFilter:="JSON Files (*.json), *.json")
If fileSaveName <> False Then
fileNumber = FreeFile
Open fileSaveName For Output As fileNumber
Print #fileNumber, JsonConverter.ConvertToJson(collectionToJson, Whitespace:=2)
Close fileNumber
End If
End Sub
In my case I chose to place the code inside a function which is called upon button click, but you could use another method of activation. When called, this function prepares the JSON and then opens a save window to save the sheet as a JSON file. It will be saved as an array containing objects with the header row items as properties.
That’s it! I hope you find this tutorial useful.
hi thanks a lot so simple but please let me know code to generate json with telugu text from excel,
Interesting article, thank you for sharing it. It’s a good approach for Excel and json conversion. I have found also this solution on the internet https://dev-bay.com/json-to-excel/ which also works good – but here one question about that, is it a good approach to flatter JSON like this tool does?
This code works really well, but I’m running into issues with large amounts of data. Are there limitations on file size or numbers of rows in an Excel file?
Hi. When I try to convert Dates into JSON, it returns output based on UTC time.
For example: I am in Eastern time Canada. I converted dates that were in ET but the output came out as UTC time. So the values are different than what was there eon the excel file. Was this a conscious effort?
Generally that is how Excel stores dates. I’m surprised you got a formatted date at all, and not just an integer timestamp to be honest. I don’t see any option in VBA-JSON to address that so you could play around with Excel but this may be something you need to run another script on your output on to fix. I don’t have experience using this with dates unfortunately.
I am not sure what I am doing wrong but I followed everything to a T and I get a ;
Run-time error ‘9’: Subscript out of range
For Each c In ActiveSheet.ListObjects(1).HeaderRowRange.Cells
Is highlighted in Yellow.
Any idea what I could be doing wrong?
Using Microsoft® Excel® for Microsoft 365 MSO (16.0.14228.20200) 32-bit
Are you certain that your data is formatted as part of an Excel table? If not, you will have to convert it to a table first. Otherwise, you won’t have any list objects, meaning it can’t find list object one.
Hii Do you get any solution for this, i am also stuck in these same