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.