Export Data to JSON File with Excel VBA

Goal

We will create a VBA macro that reads data from an Excel worksheet and converts it into a JSON file format. JSON (JavaScript Object Notation) is commonly used to store and transmit data in a lightweight and readable format, making it easy for developers and applications to work with.

Steps

1. Read Data from Excel: We will iterate through the rows and columns of the Excel worksheet and collect the data.
2. Format the Data as JSON: We’ll then format this data into JSON.
3. Write the JSON to a File: Finally, we’ll export this JSON data into a .json file.

Code Implementation

1. Basic Setup

We will first define a subroutine to export the data.

Sub ExportDataToJSON()
    Dim ws As Worksheet
    Dim rng As Range
    Dim json As String
    Dim row As Range
    Dim cell As Range
    Dim colHeaders() As String
    Dim dataArray() As Variant
    Dim i As Integer
    Dim j As Integer   
    ' Set the worksheet from which data will be exported
    Set ws = ThisWorkbook.Sheets("Sheet1")  ' Adjust the sheet name accordingly   
    ' Define the range of data to be exported
    Set rng = ws.UsedRange   
    ' Initialize the JSON string
    json = "["
    ' Get column headers from the first row
    colHeaders = Application.Transpose(rng.Rows(1).Value)
    ' Start iterating over rows (from second row onward)
    For Each row In rng.Rows
        ' Skip the header row (first row)
        If row.Row > 1 Then
            ' Open a JSON object for the current row
            json = json & "{"
            ' Iterate through the columns in the current row
            For j = 1 To rng.Columns.Count
                ' Add each cell's data as a key-value pair
                json = json & """" & colHeaders(j) & """: """ & row.Cells(1, j).Value & """"
                ' Add a comma separator if not the last column
                If j < rng.Columns.Count Then
                    json = json & ","
                End If
            Next j           
            ' Close the current JSON object
            json = json & "}"           
            ' Add a comma separator if not the last row
            If row.Row < rng.Rows.Count Then
                json = json & ","
            End If
        End If
    Next row   
    ' Close the JSON array
    json = json & "]"   
    ' Write the JSON to a file
    Dim filePath As String
    filePath = Application.GetSaveAsFilename(FileFilter:="JSON Files (*.json), *.json")   
    If filePath <> "False" Then
        ' Create and open the file
        Dim jsonFile As Integer
        jsonFile = FreeFile       
        Open filePath For Output As jsonFile
        Print #jsonFile, json
        Close jsonFile
        MsgBox "Data successfully exported to JSON!", vbInformation
    End If
End Sub

Explanation of Code

1. Setting Up the Worksheet and Range:

Set ws = ThisWorkbook.Sheets("Sheet1")
Set rng = ws.UsedRange

• ThisWorkbook refers to the workbook where the VBA code is located.
• ws is the worksheet from which you want to export data.
• UsedRange refers to the entire used range of the worksheet, which includes all cells with data.

2. Getting the Headers:

colHeaders = Application.Transpose(rng.Rows(1).Value)

• The first row (rng.Rows(1)) contains the column headers.
• Application.Transpose converts the row data into a vertical array so that we can use these as keys in our JSON object.

3. Iterating Over the Rows:

• The outer loop iterates over each row of the UsedRange.
• The inner loop iterates over each column in the row. For each column, it creates a key-value pair where the key is the column header, and the value is the data in the cell.
• The rows are wrapped in curly braces {} to represent a JSON object, and commas are added to separate each key-value pair.

4. Exporting the JSON Data:

Dim filePath As String
filePath = Application.GetSaveAsFilename(FileFilter:="JSON Files (*.json), *.json")
If filePath <> "False" Then
    Dim jsonFile As Integer
    jsonFile = FreeFile
    Open filePath For Output As jsonFile
    Print #jsonFile, json
    Close jsonFile
    MsgBox "Data successfully exported to JSON!", vbInformation
End If

• This code opens a file save dialog and asks the user where to save the .json file.
• The FreeFile function returns a file number to create and write to the file.
• We use Open to create or open the file, Print to write the JSON string, and Close to close the file.

Additional Considerations

1. Handling Different Data Types: This code assumes that all data in the worksheet is text. If you have numbers or dates, you may want to adjust the formatting before appending them to the JSON string.
2. Large Data Sets: If your dataset is very large, you might want to consider optimizing the code or handling the data in chunks to avoid memory issues.
3. Error Handling: For production code, you should add error handling to ensure that file operations and data parsing are robust.
4. Customization: If you want to structure the JSON in a nested or hierarchical way, you will need to modify the logic to group data differently (e.g., by categories or related rows).

Conclusion

This code provides a robust, step-by-step method for exporting data from Excel to JSON using VBA. By reading the data from a worksheet, converting it to the JSON format, and saving it to a file, this solution can be tailored to suit a variety of use cases where data needs to be exported from Excel in a structured and widely-used format like JSON.