Save Excel Table to JSON File using VBA and VBA-JSON

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.