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

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
    collectionToJson.Add jsonObject

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.

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

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

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

Leave a Reply

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