[Excel VBA] How to Convert Excel Cell Ranges to JSON Format and Output It

When integrating with Web APIs or exchanging data with modern web applications, JSON (JavaScript Object Notation) is standard. The need to convert data managed in Excel into this JSON format is increasing year by year.

This article explains a custom function that converts Excel table data into a standard “array of objects” JSON string using only basic VBA functions without relying on external libraries, along with an explanation of how it works.

JSON Basic Structure and Conversion Goal

First, let’s confirm the basic structure of JSON and what this code aims to achieve.

Example of Excel Source Data

UserID	UserName	Region
101	Sato Ichiro	Tokyo
102	Suzuki Hanako	Osaka

Converted JSON (Goal)

[
  {
    "UserID":"101",
    "UserName":"Sato Ichiro",
    "Region":"Tokyo"
  },
  {
    "UserID":"102",
    "UserName":"Suzuki Hanako",
    "Region":"Osaka"
  }
]

We will create a structure where multiple “objects” enclosed in curly braces {} are contained within an “array” enclosed in square brackets [].

Completed VBA Code

Here is a function (RangeToJson) that takes an Excel range as an argument and returns a JSON string, along with a demo procedure that uses it to actually output a .json file.

' Main Function: Converts a Range object to a JSON string
Function RangeToJson(ByVal sourceRange As Range) As String

    ' Declare variables
    Dim jsonString As String
    Dim headerArray As Variant
    Dim rowDataArray As Variant
    Dim r As Long, c As Long

    ' 1. Get the header row (JSON Keys) as a 1D array
    headerArray = WorksheetFunction.Transpose(WorksheetFunction.Transpose(sourceRange.Rows(1).Value))
    
    ' 2. Start of JSON array
    jsonString = "[" & vbCrLf
    
    ' 3. Loop through data rows (starting from row 2)
    For r = 2 To sourceRange.Rows.Count
        ' Get current row data as a 1D array
        rowDataArray = WorksheetFunction.Transpose(WorksheetFunction.Transpose(sourceRange.Rows(r).Value))
        
        ' 4. Assemble Key:Value pairs
        For c = 1 To UBound(headerArray)
            rowDataArray(c) = """" & headerArray(c) & """:""" & rowDataArray(c) & """"
        Next c
        
        ' 5. Assemble one JSON object and add it to the main string
        jsonString = jsonString & "  {" & Join(rowDataArray, ",") & "}," & vbCrLf
    Next r
    
    ' 6. Remove the extra comma and newline from the last object
    jsonString = Left(jsonString, Len(jsonString) - 3)
    
    ' 7. End of JSON array
    jsonString = jsonString & vbCrLf & "]"
    
    ' Return the completed JSON string
    RangeToJson = jsonString
    
End Function


' Execution Demo: Uses the above function to create a JSON file
Sub Demo_CreateJsonFile()
    Dim targetRange As Range
    Dim jsonOutput As String
    Dim streamObj As Object
    Dim outputFilePath As String
    
    ' Excel range to convert to JSON
    Set targetRange = ThisWorkbook.Worksheets("Sheet1").Range("A1:C3")
    
    ' Call the function to get the JSON string
    jsonOutput = RangeToJson(targetRange)
    
    ' Write to file in UTF-8 using ADODB.Stream
    outputFilePath = ThisWorkbook.Path & "\data.json"
    Set streamObj = CreateObject("ADODB.Stream")
    With streamObj
        .Type = 2
        .Charset = "UTF-8"
        .Open
        .WriteText jsonOutput
        .SaveToFile outputFilePath, 2
        .Close
    End With
    
    Set streamObj = Nothing
    MsgBox "JSON file creation is complete."
End Sub

Key Points Explanation

1. Array Conversion of Header and Data Rows

headerArray = WorksheetFunction.Transpose(WorksheetFunction.Transpose(sourceRange.Rows(1).Value))

By using WorksheetFunction.Transpose twice, we convert a single row of Excel cells (sourceRange.Rows(1)) into a 1-dimensional array that is easy to handle in VBA. This is done for the header row (JSON keys) and each data row for later processing.

2. Assembling Key:Value Pair Strings

rowDataArray(c) = """" & headerArray(c) & """:""" & rowDataArray(c) & """"

This is the most critical part of generating the JSON string. It assembles the string in the format “Key”:”Value”.

To represent a double quote character ” within a VBA string literal, you must type it twice as “”. Therefore, to enclose a string in double quotes, you need to write “””” (four times). This single line dynamically creates string parts like “UserID”:”101″.

3. Assembling Objects and Arrays

jsonString = jsonString & "  {" & Join(rowDataArray, ",") & "}," & vbCrLf

The array of "Key":"Value" parts created in the inner loop is concatenated with commas using the Join function. By enclosing this in {}, one row of data becomes a single JSON object. Finally, a comma , and a newline are added to prepare for the next object.

4. Removing the Trailing Comma

jsonString = Left(jsonString, Len(jsonString) - 3)

As we add JSON objects in the loop, the last object will also have an extra comma and newline (, vbCrLf) attached to it. This is invalid JSON syntax.

Therefore, after the loop finishes, we remove these extra characters (3 characters) from the end of the string using the Left function to ensure correct JSON formatting.

Summary

Generating JSON with VBA is essentially precise string manipulation.

• Use arrays to handle Excel row data efficiently.
• Use nested loops to process each key and value for every object.
• Use string concatenation to correctly place double quotes and various symbols.
• Finally, remove unnecessary characters to format the whole string.

This manual method helps you deeply understand the structure of JSON. If you need to handle more complex JSON, you might also consider using dedicated libraries (like VBA-JSON).