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
You need your data to be in a table. Go to insert -> table -> select the area with your data and click ok. Try generating the Json again. It should work!!!!
How should string data be expressed in an Excel cell so that it is output as a JSON array?
This was very helpful thank you. I would like to add to my code though. How would I be able to convert the hyperlinks in my excel file through VBA to have them be a part of my JSON?
Great tutorial. Really loved the micro explanations everywhere. Helps people like myself who learned via sink or swim.
I am trying to do a very specific thing… export to nested json. The output is very specific (NFT metadata) and requires a list of attributes to be an defined array of names and values. It’s something like this:
{“title”:”TITLE”,”description”:”The \”Description\” of the thing.”,attributes:[{“name”:”collection”,”value”:”classic v1″},{“name”:”power”,”value”:”9001″,”type”:”number”}],”type”:”proton”,”additionalFiles”:[“FILEPATH1″,”FILEPATH2″],”royalties”:”1000″}
Then that row would need to be exported as its own json file using a leading zeros format based on the ID column.
The only successful implementation I have seen (without multiple file export) was NiftyKit’s done in Google Sheets with gs code, but gs (and JS derivatives in general) doesn’t convert well to vba for use with my data. They use dots in the header row to signify how to nest the data. For instance, “attributes.0.name” defines “name” in the 0th array of attributes… {…”attributes”:[…{…”name”:”_NAME_”…}_(=[0])…]…}
I’m not sure if the library is set up to handle that sort of mess, but if you could point me the right functions to use, that would be helpful. For more I formation on this style of metadata, check out OpenSea’s docs on the topic.
As far as I know you can create that JSON structure in VBA and then just use ConvertToJson on it. If you aren’t sure how to represent it in VBA, write a mock JSON first with the format you want, import it using the same library, and check how it represented it.
Tough part here is probably representation in Excel. Sometimes you would need multiple tables to represent data like this. Another way to go about it, which is essentially a one-to-many relationship is just to duplicate the data in the table. So Title, description, name, value, etc is all a column and title is just re-printed for each row. Best of luck.