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.

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

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

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

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

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

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

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

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

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

Comments are closed.