Convert Excel to JSON Using VBA

24 Aug Convert Excel to JSON Using VBA

Howdee! I had a lot of requests after my recent article on converting a JSON string to Excel, asking if it was possible to convert Excel to JSON. Fortunately, it is not only possible, but you can use the same library we previously covered to do so! In this article, we will go over how to convert your Excel data to a JSON string, and how to convert that same data to a JSON file. If you haven’t read the previous article linked above, I highly recommend you do so before reading on here. Beyond covering how to import JSON, it also covers the basics of JSON objects and arrays. With that said, let’s dive in!

Convert Excel to JSON String

The most common occurrence for me, is the need to transfer some sort of data from an Excel file to a JSON string so I can pass that information through a HTTP request or to another program. For this, you’ll need to convert Excel to JSON, and store that in a variable. To do that, we need to dimension a few variables to use later:

Dim excelRange As Range
Dim jsonItems As New Collection
Dim jsonDictionary As New Dictionary
Dim i As Long
Dim cell As Variant

The first variable, excelRange, will be used to define the range of data we want to convert to JSON. The second, jsonItems, will be the collection we ultimately store our data in. Collection might be a new term to some so, at a high level, a collection is just an object that we can use to group and manage related objects. The third variable, jsonDictionary, is the dictionary we will first store our Excel values in. A dictionary is a type of collection that you can access using a key. Again, check out the previous post on JSON to get a better understanding of this type of object. The last two variables, i & cell, will be used in our for loop to help store the values to a dictionary. The next bit of our code looks like this:

Set excelRange = Cells(1, 1).CurrentRegion

For i = 2 To excelRange.Rows.Count
    jsonDictionary("id") = Cells(i, 1)
    jsonDictionary("name") = Cells(i, 2)
    jsonDictionary("username") = Cells(i, 3)
    jsonDictionary("email") = Cells(i, 4)
    jsonDictionary("street") = Cells(i, 5)
    jsonDictionary("suite") = Cells(i, 6)
    jsonDictionary("city") = Cells(i, 7)
    jsonDictionary("zipcode") = Cells(i, 8)
    jsonDictionary("phone") = Cells(i, 9)
    jsonDictionary("website") = Cells(i, 10)
    jsonDictionary("company") = Cells(i, 11)

    jsonItems.Add jsonDictionary
    Set jsonDictionary = Nothing
Next i

MsgBox JsonConverter.ConvertToJson(jsonItems, Whitespace:=3)

So what is happening here? Firstly, I start by setting the range where our data exists. Now that I know the size of the range, I can begin to convert Excel to JSON by looping through each row (skipping headers) and writing each cell’s value into the dictionary. At the end of each loop, I need to write the dictionary to my collection and then clear it for the next loop. Lastly, I’m printing this JSON string to a message box so we can view it. You can do whatever you wish with the JSON string at this point, I’m only printing to a message box to demonstrate how to convert Excel to JSON string.

Convert Excel to JSON File

Converting Excel data to a JSON file is almost the exact same process. The only difference is, instead of outputting the result to a string variable or writing it to a cell in the workbook, you’re going to use the FileSystemObject model to create a new file to convert & store Excel data to a JSON file. You’ll need these two new variables:

Dim jsonFileObject As New FileSystemObject
Dim jsonFileExport As TextStream

The first allows you to access the FileSystemObject model which will allow you to create new files and save them to a folder. The second allows you to write text, in this case the JSON string, to the file. Those two new lines of code look like this:

Set jsonFileExport = jsonFileObject.CreateTextFile("C:\Users\username\Desktop\jsonExample.json", True)
jsonFileExport.WriteLine (JsonConverter.ConvertToJson(jsonItems, Whitespace:=3))

The first will create the new JSON file. Make sure you use the keyword “True” if you’re planning to reuse this code. This will allow the program to overwrite the file. However, if you’re having the user create the name of the file, it might be useful to use “False” which will not allow the program to create any new file that would overwrite a previous one.

The second line of code simply writes the converted JSON to the file. Unless you open the file in your code, there is no need to explicitly save or close the file in this example. The full code to convert Excel to JSON looks like this:

Sub excelToJsonFileExample()
Dim excelRange As Range
Dim jsonItems As New Collection
Dim jsonDictionary As New Dictionary
Dim jsonFileObject As New FileSystemObject
Dim jsonFileExport As TextStream
Dim i As Long
Dim cell As Variant

Set excelRange = Cells(1, 1).CurrentRegion

For i = 2 To excelRange.Rows.Count
    jsonDictionary("id") = Cells(i, 1)
    jsonDictionary("name") = Cells(i, 2)
    jsonDictionary("username") = Cells(i, 3)
    jsonDictionary("email") = Cells(i, 4)
    jsonDictionary("street") = Cells(i, 5)
    jsonDictionary("suite") = Cells(i, 6)
    jsonDictionary("city") = Cells(i, 7)
    jsonDictionary("zipcode") = Cells(i, 8)
    jsonDictionary("phone") = Cells(i, 9)
    jsonDictionary("website") = Cells(i, 10)
    jsonDictionary("company") = Cells(i, 11)

    jsonItems.Add jsonDictionary
    Set jsonDictionary = Nothing
Next i

‘change the path below to save where you desire
Set jsonFileExport = jsonFileObject.CreateTextFile("C:\Users\username\Desktop\jsonExample.json", True)
jsonFileExport.WriteLine (JsonConverter.ConvertToJson(jsonItems, Whitespace:=3))

End Sub

Here is the outputted JSON file displayed in Visual Studio Code:

I hope this tutorial was useful for those of you that need to write your Excel data to JSON either to a string or to a file. If you have questions, drop them in the comments below. As always, the example file is available for your download if you’re a registered member to this site. The file contains the examples I walked through today to convert Excel to JSON. Registering is free and only takes a minute. You’ll get notified as I release additional content but I won’t spam you with tons of unsolicited emails. Until next time!

Cheers,

R