Convert the Excel to JSON using VBA code

Instead of downloading the Excel to JSON conversion software, you can also use the VBA Code Editor to convert the Excel data to JSON format. You can create your own customized code on VBA editor and run it to get the task done.

VBA Code Editor is a developer tool or in-built feature of Excel. It comes default with Excel when you download MS Excel. In VBA code Editor, you can write your own code and perform a particular task. If you are comfortable in doing coding, then it's a good way for you.

Make a code for Excel to JSON conversion by mapping of Excel columns to JSON object keys. Then execute the code to convert the data into JSON format. One who knows coding in visual basics then use this tool.

Why VBA code editor?

If you are good at coding and create logical codes, then why go for tools. Create your own customized code in VBA code editor and convert any number of Excel documents to JSON format by running that code. It does not need to download or internet connection and all.

You do not need to explicitly download any tool to convert your Excel data to JSON format. While you can do it without downloading any software on your system using Excel VBA code Editor. If you are comfortable in doing coding, then it's a good way for you. Even you do not need to install the VBA editor as it comes with Excel by default.

Tip: Your Excel data must have column names because the first row is always considered as a header.

Open VBA editor

You can open the VBA code editor in Excel from the Developer tab.
In case the developer tab is not available in your Excel ribbon, customize the ribbon by right-clicking on the ribbon and choose the developer tab from the list.
Then, click the first option Visual Basic to open the VBA code editor.
In VBA code editor, create a new module under your currently opened Excel file.
Write the Excel to JSON conversion code here.

VBA Code

Following is the complete code for the conversion of the Excel file data to JSON format. Copy this following code and paste this to your respective VBA code editor.

Public Function ExcelToJSON(rng As Range) As String
    ' Check there must be at least two columns in the Excel file
    If rng.Columns.Count < 2 Then
        ExcelToJSON = CVErr(xlErrNA)
        Exit Function
    End If
    Dim dataLoop, headerLoop As Long
    ' Get the first row of the Excel file as a header 
    Dim headerRange As Range: Set headerRange = Range(rng.Rows(1).Address)   
    ' Count the number of columns of targeted Excel file
    Dim colCount As Long: colCount = headerRange.Columns.Count   
    Dim JSON As String: JSON = "["    
    For dataLoop = 1 To rng.Rows.Count
        ' Skip the first row of the Excel file because it is used as header
        If dataLoop > 1 Then
            ' Start data row
            Dim jsonData As String: jsonData = "{"            
            ' Loop through each column and combine with the header
            For headerLoop = 1 To colCount
                jsonData = jsonData & """" & headerRange.Value2(1, headerLoop) & """" & ":"
                jsonData = jsonData & """" & rng.Value2(dataLoop, headerLoop) & """"
                jsonData = jsonData & ","
            Next headerLoop            
            ' Strip out the comma in last value of each row
            jsonData = Left(jsonData, Len(jsonData) - 1)            
            ' End data row
            JSON = JSON & jsonData & "},"
        End If
    Next    
    ' Strip out the last comma in last row of the Excel data
    JSON = Left(JSON, Len(JSON) - 1)   
    JSON = JSON & "]"    
    ExcelToJSON = JSON
End Function

In this VBA code, we have defined a function named ExcelToJSON that will later be used in an Excel file for the conversion of Excel to JSON. Let's see how it will execute.

Syntax

This function will take the range of Excel cells, e.g., A1:F9 that you would like to convert to JSON format.

Convert the Excel data to JSON format

Now, we will use the function ExcelToJSON() that we have created using the above code to convert the Excel data to JSON format. Remember one thing - this function is created only for this particular file. It is not available for other Excel files.

Go back to the Excel file and write the following ROUND() formula in an Excel cell where you want to place the converted JSON result. For example,
=ExcelToJSON(A1:D3)

Currently, we are converting the first two Excel rows and four columns data into JSON format only.
Press the Enter key and get the result and see that the data is successfully converted into JSON form.

In the same way, you can use this function for more data conversion by increasing the range of the Excel cells. Copy this converted JSON data from here and store it simple Word file.

Code Explanation

Code 1

If rng.Columns.Count < 2 Then
        ExcelToJSON = CVErr(xlErrNA)
        Exit Function
End If

First of all, this code is used to verify that the selected range must have at least two columns for Excel to JSON conversion. If not, you will be exit from this function.

Code 2

Dim dataLoop, headerLoop As Long
' Get the first row of the Excel file as a header 
Dim headerRange As Range: Set headerRange = Range(rng.Rows(1).Address)

Next, we have created two variables: dataLoop and headerLoop. The dataLoop variable for the Excel data stored in Excel file, and headerLoop for the column header. Both variables will use during conversion.

Code 3

' Count the number of columns of targeted Excel file
Dim colCount As Long: colCount = headerRange.Columns.Count

Here, we have created a variable colCount. We have used this code to find the number of targeted columns to convert their data to JSON format in an Excel file.

Code 4

Dim JSON As String: JSON = "["   
For dataLoop = 1 To rng.Rows.Count
       ' Skip the first row because it is used as header
        If dataLoop > 1 Then
             ' Start data row
             Dim jsonData As String: jsonData = "{"

Now, this code starts creating JSON data from Excel file. Here, dataLoop variable is used inside a for loop from 1 to end of the selected rows. The first row of the Excel file data will be skipped because while converting Excel data to JSON first row always be considered as a header.

The data in JSON format will be enclosed between curly {} braces.

Code 5

            ' Loop through each column and combine with the header
            For headerLoop = 1 To colCount
                jsonData = jsonData & """" & headerRange.Value2(1, headerLoop) & """" & ":"
                jsonData = jsonData & """" & rng.Value2(dataLoop, headerLoop) & """"
                jsonData = jsonData & ","
            Next headerLoop            
            ' Strip out the comma in last value of each row
            jsonData = Left(jsonData, Len(jsonData) - 1)            
            ' End data row
            JSON = JSON & jsonData & "},"
        End If

A loop will run through each column and combine the column header with row data. Each value will be separated by comma. While this code (jsonData = Left(jsonData, Len(jsonData) - 1)) will skip the comma in last value after each row.

In JSON file format, each row of data will be enclosed between {} braces.

Code 5

    ' Strip out the last comma in last row of the Excel data
    JSON = Left(JSON, Len(JSON) - 1)    
    JSON = JSON & "]"    
    ExcelToJSON = JSON

All concatenated data will be stored in a JSON name variable and this complete data will be placed between the [] square brackets. The converted JSON data will display to the user in an Excel cell where he will use this user-defined ExcelToJSON() function.

Next TopicRound formula in Excel

← prev next →