【Excel VBA】Excelのセル範囲をJSON形式に変換して出力する方法

2025年8月10日

Web APIとの連携や、最新のWebアプリケーションでデータをやり取りする際、**JSON（JavaScript Object Notation）**形式のデータが標準的に使われます。Excelで管理しているデータを、このJSON形式に変換して出力したい、というニーズは年々高まっています。

この記事では、外部ライブラリを使わず、VBAの基本的な関数だけでExcelの表データを、一般的な「オブジェクトの配列」形式のJSON文字列に変換するカスタム関数と、その仕組みを解説します。

JSONの基本構造と変換目標

まず、JSONの基本的な構造と、今回のコードが何を目指すのかを確認します。

Excelの元データ例

UserID	UserName	Region
101	佐藤一郎	東京
102	鈴木花子	大阪

Google スプレッドシートにエクスポート

変換後のJSON（目標）

[
  {
    "UserID":"101",
    "UserName":"佐藤 一郎",
    "Region":"東京"
  },
  {
    "UserID":"102",
    "UserName":"鈴木 花子",
    "Region":"大阪"
  }
]

このように、角括弧[]で全体が囲まれた「配列」の中に、波括弧{}で囲まれた「オブジェクト」が複数含まれる構造を作成します。

完成したVBAコード

Excel範囲を引数に取り、JSON文字列を返す関数（RangeToJson）と、それを使って実際に.jsonファイルを出力するデモ用のプロシージャです。

' メインの関数: RangeオブジェクトをJSON文字列に変換する
Function RangeToJson(ByVal sourceRange As Range) As String

    ' 変数を宣言
    Dim jsonString As String
    Dim headerArray As Variant
    Dim rowDataArray As Variant
    Dim r As Long, c As Long

    ' 1. ヘッダー行(JSONのKey)を1次元配列として取得
    headerArray = WorksheetFunction.Transpose(WorksheetFunction.Transpose(sourceRange.Rows(1).Value))
    
    ' 2. JSON配列の開始
    jsonString = "[" & vbCrLf
    
    ' 3. データ行をループ (2行目から)
    For r = 2 To sourceRange.Rows.Count
        ' 現在の行データを1次元配列として取得
        rowDataArray = WorksheetFunction.Transpose(WorksheetFunction.Transpose(sourceRange.Rows(r).Value))
        
        ' 4. Key:Valueペアを組み立てる
        For c = 1 To UBound(headerArray)
            rowDataArray(c) = """" & headerArray(c) & """:""" & rowDataArray(c) & """"
        Next c
        
        ' 5. 1行分のJSONオブジェクトを組み立てて、メインの文字列に追加
        jsonString = jsonString & "  {" & Join(rowDataArray, ",") & "}," & vbCrLf
    Next r
    
    ' 6. 最後のオブジェクトについている余分なカンマと改行を削除
    jsonString = Left(jsonString, Len(jsonString) - 3)
    
    ' 7. JSON配列の終了
    jsonString = jsonString & vbCrLf & "]"
    
    ' 完成したJSON文字列を返す
    RangeToJson = jsonString
    
End Function


' 実行デモ: 上記の関数を使い、JSONファイルを作成する
Sub Demo_CreateJsonFile()
    Dim targetRange As Range
    Dim jsonOutput As String
    Dim streamObj As Object
    Dim outputFilePath As String
    
    ' JSONに変換したいExcelの範囲
    Set targetRange = ThisWorkbook.Worksheets("Sheet1").Range("A1:C3")
    
    ' 関数を呼び出して、JSON文字列を取得
    jsonOutput = RangeToJson(targetRange)
    
    ' ADODB.Streamを使ってUTF-8でファイルに書き出す
    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ファイルの作成が完了しました。"
End Sub

コードのポイント解説

① ヘッダー行とデータ行の配列化

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

WorksheetFunction.Transposeを2回使うことで、Excelの1行分のセル範囲（sourceRange.Rows(1)）を、VBAで扱いやすい1次元配列に変換しています。これをヘッダー行（JSONのキー）と各データ行でそれぞれ行い、後の処理で利用します。

② Key:Valueペア文字列の組み立て

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

これがJSON文字列を生成する上で最も重要な部分です。"キー":"値"という形式の文字列を組み立てています。

VBAの文字列リテラルの中で、文字としてのダブルクォーテーション"を表現するには、""と2つ続けます。そのため、文字列の最初と最後を"で囲むには""""と4つ記述する必要があります。この一行で、例えば"UserID":"101"という文字列パーツを動的に作成しています。

③ オブジェクトと配列の組み立て

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

内側のループで作成した"キー":"値"のパーツ配列を、Join関数でカンマ区切りにして連結します。それを{}で囲むことで、1行分のデータが一つのJSONオブジェクトになります。最後に,と改行を追加して、次のオブジェクトに備えます。

④ 末尾の余分なカンマの削除

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

ループ処理でJSONオブジェクトを追加していくと、最後のオブジェクトの後ろにも余分なカンマと改行（,vbCrLf）が付いてしまいます。これはJSONの文法として正しくありません。そこで、ループが完了した後、文字列の長さから3文字分をLeft関数で取り除くことで、この余分な部分を削除し、正しいJSON形式に整えています。