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.

VBA References screenshot.
Adding a reference to the Microsoft Scripting Runtime. You can access this from Tools > References.

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.

5 thoughts on “Save Excel Table to JSON File using VBA and VBA-JSON”

  1. 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?

  2. 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?

  3. 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?

    1. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *