【Excel VBA】ExcelシートをJSON文字列に変換方法（kintoneインポート用）

ExcelのシートをVBAでJSON文字列に変換する事例です。用途はWebサービスのkintoneにAPIでインポートするときのJSON形式です。用途を限定し、VBA-JSONなどのライブラリを使用しない基本的な方法での事例です。

内容：

１．前提条件

作成するJSON文字列は、kintone REST APIの「複数のレコードを登録する」の仕様にあわせた次のようなJSON文字列です。

{
  "app": 1,
  "records": [
    {
      "文字列1行_0": {
        "value": "ABC"
      },
      "文字列1行_1": {
        "value": "ABC"
      }
    },
    {
      "文字列1行_0": {
        "value": "EFG"
      },
      "文字列1行_1": {
        "value": "EFG"
      }
    }
  ]
}

複数のレコードを登録する - cybozu developer network

２．アウトプットイメージ

変換対象のExcelのシートの状態と、JSON文字列に変換後のイメージは次のとおりです。なお、コードをシンプルにするため、JSON文字列には改行を含みません（kintoneへのインポートは可能です）。

ExcelシートをJSON文字列に変換イメージ

３．VBAサンプル

汎用性を持たせるため、アプリID、対象ファイルパス、データ範囲の「最終行、最終列」を指定して利用するコードです。

この事例の設定値は以下です（※ 利用状況にあわせて修正）

アプリID:　140 

対象ファイルパス:　E:\登録データ.xlsx

最終行:　4

最終列:　5

対象ファイルのアクティブシートで、セル１行１列から指定した最終行最終列の範囲を変換します。

なお、kintone REST APIで一度に登録できるレコードは100件までなので、最終行は100行以内とします。

Sub メイン()

    JSON文字列 = JSON変換(140, "E:\登録データ.xlsx", 4, 5)
    MsgBox JSON文字列
    
End Sub

Function JSON変換(アプリID As Long, 対象ファイルパス As String, 最終行 As Long, 最終列 As Long)
   
'初期設定'
    
    Dim 第2階層 As Variant
    Dim 第3階層 As Variant
    
    '対象ファイルを開いて、配列に格納'
    Set 対象ファイル = Workbooks.Open(対象ファイルパス)
    配列データ = Range(対象ファイル.ActiveSheet.Cells(1, 1), 対象ファイル.ActiveSheet.Cells(最終行, 最終列)).Value
    対象ファイル.Close SaveChanges:=False
    行数 = UBound(配列データ, 1)
    列数 = UBound(配列データ, 2)
    
    ReDim 第2階層(1 To 行数 - 1) As Variant
    ReDim 第3階層(1 To 列数) As Variant

'配列データをループ処理'
    For 行 = 2 To 行数
        For 列 = 1 To 列数
            第3階層(列) = """" & 配列データ(1, 列) & """:{""value"":""" & 配列データ(行, 列) & """}"
        Next 列
        第2階層(行 - 1) = "{" & Join(第3階層, ",") & "}"
    Next 行
    
'kintone用に変換'
    JSON変換 = "{""app"": """ & アプリID & """,""records"": [" & Join(第2階層, ",") & "]}"

End Function

上記サンプルは、 メッセージボックスで、変換内容を表示させていますが、この値をkintone REST APIに用いることで、Excelシートの取り込みができました。

以上、Excel VBAでExcelシートをJSON文字列に変換方法（kintoneインポート用）でした。