記事ID: 108
タイトル: 【VBA】JSONデータをExcelに読み込み・書き出しする方法（コピペOK）
カテゴリ: 外部連携
一次キーワード: VBA JSON 読み込み 書き出し
想定読者: 外部システムとJSON形式でデータをやり取りする必要がある実務担当者（初心者〜初級者）
検索意図: VBAでJSONファイルを読み込んでExcelに展開したい / ExcelのデータをJSON形式で書き出したい
読者の悩み（1文）: 外部システムからJSON形式のデータを受け取ったが、VBAでどう扱えばいいか分からない
読了後にできること（1文）: VBAの標準機能だけでJSONの読み込み・書き出しが自動化できる
前提条件:
- Excel版: Excel 2016以降 / Microsoft 365
- OS: Windows 10/11
- 保存形式: .xlsm（マクロ有効ブック）
- 貼り付け場所: 標準モジュール
- 実行方法: マクロ実行（Alt+F8）
更新日: 2026-03-18
—
この記事でわかること
- JSONファイルを読み込んで、Excelのセルに表形式で展開できる
- Excelの表データをJSON形式のファイルに書き出せる
- 外部ライブラリ不要。VBAの標準機能だけで動く（64bit Excel対応）
対象: Excel 2016以降 / Microsoft 365、Windows 10/11
どんな場面で使う？
- Web APIから取得したJSONデータをExcelの表に展開したいとき
- Excelの一覧表を他システム向けにJSON形式で出力したいとき
- 外部ライブラリなしで64bit Excelでも動くJSON処理が必要なとき
- JSON形式のマスタデータを毎日自動でExcelに取り込みたいとき
—
完成イメージ（Before / After）
Before（JSONファイルの中身）:
[
{"社員番号": "E001", "氏名": "田中太郎", "部署": "製造部", "売上": 1500000},
{"社員番号": "E002", "氏名": "鈴木花子", "部署": "品質管理部", "売上": 1200000},
{"社員番号": "E003", "氏名": "佐藤一郎", "部署": "総務部", "売上": 800000}
]
After（Excelシートに展開）:
| A列（社員番号） | B列（氏名） | C列（部署） | D列（売上） |
|---|---|---|---|
| E001 | 田中太郎 | 製造部 | 1500000 |
| E002 | 鈴木花子 | 品質管理部 | 1200000 |
| E003 | 佐藤一郎 | 総務部 | 800000 |
逆に、Excelの表からJSONファイルを書き出すこともできる。
—
自分も以前、外部システムから毎日JSONファイルが届くようになったとき、中身をExcelに手作業で転記していた。キーと値のペアを1つずつコピペするのが地味にストレスだった。VBAでJSON読み込みを自動化してからは、ファイルを指定して実行するだけで表が完成するようになった。毎朝15分の転記作業がゼロになった。JSONの見た目は独特で最初は戸惑うけど、コツさえ掴めばCSVと同じ感覚で扱える。この記事でその壁を越えてもらえればうれしい。
VBAの標準機能だけで、JSONの読み込みも書き出しもできる。外部ライブラリは不要。
なお、JSONファイルの読み込みはテキストファイルの取り込みが基本になる。テキストファイルの扱いに慣れていない場合は テキストファイル（txt/log）をExcelに取り込む方法 を先に読むと理解しやすい。
—
実行前の準備
JSONとは
JSON（JavaScript Object Notation）は、データを「キーと値のペア」で表現するテキスト形式。Web APIや外部システムとのデータ交換で広く使われている。
{"キー": "値", "キー2": 123}
{}がオブジェクト（1件分のデータ）[]が配列（複数件のデータ）- キーはダブルクォートで囲む
- 値は文字列（ダブルクォート）、数値、true/false、null
バックアップを取る
読み込み処理はアクティブシートのセルを上書きする。必ずファイルのコピーを別フォルダに保存してから実行する。
Excelをマクロ有効ブック（.xlsm）で保存する
拡張子が .xlsx のままだとマクロが保存できない。
- 「ファイル」→「名前を付けて保存」
- ファイルの種類を「Excelマクロ有効ブック (*.xlsm)」に変更
- 保存
Excelが32bitか64bitか確認する
JSONの読み込み方法はExcelのビット数で異なる場合がある。確認方法：
- Excelを開く
- 「ファイル」→「アカウント」→「Excelのバージョン情報」をクリック
- ダイアログに「32ビット」または「64ビット」の表記がある
この記事のコードは64bit / 32bit どちらでも動く。
—
手順（コピペ → 実行まで約5分）
VBE（コードを書く画面）を開く
- Excelで
Alt + F11を押す
標準モジュールを挿入する
- VBEのメニュー →「挿入」→「標準モジュール」
コードを貼り付けて実行する
- コードウィンドウに、下のコードをそのままコピペする
- コード内のファイルパスを自分の環境に合わせて書き換える
Alt + F8→ マクロ名を選んで「実行」
サンプルJSONファイルを用意する
テスト用に以下の内容をメモ帳に貼り付けて、sample.json として保存する。
- メモ帳を開く
- 下のJSONをそのまま貼り付ける
- 「ファイル」→「名前を付けて保存」
- ファイル名:
sample.json - 文字コード（エンコード）: UTF-8 を選択（Windows 11のメモ帳はデフォルトでUTF-8。Windows 10の場合は「文字コード」欄で明示的にUTF-8を選ぶ）
- 保存先:
C:\temp\（なければフォルダを作成）
[
{"社員番号": "E001", "氏名": "田中太郎", "部署": "製造部", "売上": 1500000},
{"社員番号": "E002", "氏名": "鈴木花子", "部署": "品質管理部", "売上": 1200000},
{"社員番号": "E003", "氏名": "佐藤一郎", "部署": "総務部", "売上": 800000}
]
—
コード（最小版）– JSONファイルを読み込んでセルに展開
フラットなJSON配列（[{...}, {...}, ...]）を読み込んで、アクティブシートに表形式で展開する。64bit Excel でも動く自作パーサー方式。
以下のコードをすべてコピーして、1つの標準モジュールに貼り付ける。 メインの ImportJsonMinimal と補助関数 SplitJsonPairs・CleanJsonString の3つがセットで必要。
'============================================================
' ■ JSONファイル → Excel読み込み（最小版）
' → フラットなJSON配列をアクティブシートに展開
' → 64bit / 32bit 両対応（外部ライブラリ不要）
'============================================================
Sub ImportJsonMinimal()
'--- ★書き換えポイント: JSONファイルのパス ---
Dim filePath As String
filePath = "C:\temp\sample.json" '← 自分の環境に合わせて変更
'--- ファイル存在確認
If Dir(filePath) = "" Then
MsgBox "ファイルが見つかりません:" & vbCrLf & filePath, vbExclamation
Exit Sub
End If
'--- UTF-8でファイルを読み込む（ADODB.Stream）
Dim stream As Object
Set stream = CreateObject("ADODB.Stream")
stream.Charset = "UTF-8"
stream.Open
stream.LoadFromFile filePath
Dim jsonText As String
jsonText = stream.ReadText(-1) '← -1 = 全文読み込み
stream.Close
Set stream = Nothing
'--- BOM除去（先頭にBOMが付いている場合）
If Len(jsonText) > 0 And AscW(Left(jsonText, 1)) = 65279 Then
jsonText = Mid(jsonText, 2)
End If
'--- JSON配列をパースしてシートに展開
Dim ws As Worksheet
Set ws = ActiveSheet
'--- 改行・タブを除去して1行にする
jsonText = Replace(jsonText, vbCrLf, "")
jsonText = Replace(jsonText, vbLf, "")
jsonText = Replace(jsonText, vbTab, "")
'--- 先頭・末尾の [] を除去
jsonText = Trim(jsonText)
If Left(jsonText, 1) = "[" Then jsonText = Mid(jsonText, 2)
If Right(jsonText, 1) = "]" Then jsonText = Left(jsonText, Len(jsonText) - 1)
'--- オブジェクト単位に分割: }{ を区切りとして分割
Dim objDelimiter As String
objDelimiter = "}<<SEP>>{"
jsonText = Replace(jsonText, "},{", objDelimiter)
Dim records() As String
records = Split(jsonText, "<<SEP>>")
'--- ヘッダー（キー名）を1件目から取得
Dim firstRecord As String
firstRecord = records(0)
If Left(firstRecord, 1) = "{" Then firstRecord = Mid(firstRecord, 2)
If Right(firstRecord, 1) = "}" Then firstRecord = Left(firstRecord, Len(firstRecord) - 1)
'--- キー名を抽出
Dim keys() As String
Dim keyCount As Long
keyCount = 0
Dim pairs() As String
pairs = SplitJsonPairs(firstRecord)
keyCount = UBound(pairs) + 1
ReDim keys(0 To keyCount - 1)
Dim i As Long
Dim colonPos As Long
For i = 0 To UBound(pairs)
colonPos = InStr(pairs(i), ":")
If colonPos > 0 Then
keys(i) = CleanJsonString(Left(pairs(i), colonPos - 1))
End If
Next i
'--- ヘッダー行を書き込み
Dim col As Long
For col = 0 To keyCount - 1
ws.Cells(1, col + 1).Value = keys(col)
Next col
'--- データ行を書き込み
Dim r As Long
For r = 0 To UBound(records)
Dim rec As String
rec = records(r)
If Left(rec, 1) = "{" Then rec = Mid(rec, 2)
If Right(rec, 1) = "}" Then rec = Left(rec, Len(rec) - 1)
Dim vals() As String
vals = SplitJsonPairs(rec)
For col = 0 To UBound(vals)
colonPos = InStr(vals(col), ":")
If colonPos > 0 Then
Dim cellValue As String
cellValue = CleanJsonString(Mid(vals(col), colonPos + 1))
'--- 数値なら数値として書き込み
If IsNumeric(cellValue) Then
ws.Cells(r + 2, col + 1).Value = CDbl(cellValue)
Else
ws.Cells(r + 2, col + 1).Value = cellValue
End If
End If
Next col
Next r
MsgBox UBound(records) + 1 & " 件のデータを読み込みました。", vbInformation
End Sub
'============================================================
' ■ JSON文字列のキー:値ペアを分割する補助関数
' → ダブルクォート内のカンマを無視して分割
'============================================================
Private Function SplitJsonPairs(ByVal s As String) As String()
Dim result() As String
Dim cnt As Long
cnt = 0
ReDim result(0 To 0)
Dim inQuote As Boolean
inQuote = False
Dim escaped As Boolean
escaped = False
Dim startPos As Long
startPos = 1
Dim pos As Long
Dim c As String
For pos = 1 To Len(s)
c = Mid(s, pos, 1)
If escaped Then
escaped = False
ElseIf c = "\" Then
escaped = True
ElseIf c = """" Then
inQuote = Not inQuote
ElseIf c = "," And Not inQuote Then
'--- カンマで区切り
If cnt > 0 Then ReDim Preserve result(0 To cnt)
result(cnt) = Trim(Mid(s, startPos, pos - startPos))
cnt = cnt + 1
startPos = pos + 1
End If
Next pos
'--- 最後のペア
If cnt > 0 Then ReDim Preserve result(0 To cnt)
result(cnt) = Trim(Mid(s, startPos))
SplitJsonPairs = result
End Function
'============================================================
' ■ JSON文字列のクリーニング（ダブルクォート除去・トリム）
'============================================================
Private Function CleanJsonString(ByVal s As String) As String
s = Trim(s)
'--- 前後のダブルクォートを除去
If Left(s, 1) = """" And Right(s, 1) = """" Then
s = Mid(s, 2, Len(s) - 2)
End If
'--- エスケープされたダブルクォートを戻す
s = Replace(s, "\""", """")
'--- エスケープされたバックスラッシュを戻す
s = Replace(s, "\\", "\")
'--- null を空文字に
If LCase(s) = "null" Then s = ""
'--- true/false はそのまま文字列として返す
CleanJsonString = s
End Function
書き換えポイント
| 変数 | 説明 | 初期値 |
|---|---|---|
filePath |
JSONファイルのフルパス | C:\temp\sample.json |
—
コード（実務版）– エラー処理付きJSON読み込み＋書き出し
JSON読み込み＋書き出しの両方を覚えてからは、Excelで加工したデータをJSON形式で返す作業もワンクリックで終わるようになった。外部チームとのやり取りが格段にスムーズになった。
JSON読み込み（実務版）
最小版にエラー処理・処理速度向上・上書き確認を追加。
※ アクティブシートのデータは上書きされます。実行前にバックアップを取ってください。
以下のコードを標準モジュールに貼り付ける。最小版で使った補助関数（SplitJsonPairs と CleanJsonString）も同じモジュールに必要。 最小版のコードを既に貼り付けている場合はそのまま使える。
'============================================================
' ■ JSONファイル → Excel読み込み（実務版）
' → エラー処理・高速化・上書き確認付き
' → 64bit / 32bit 両対応（外部ライブラリ不要）
' → ※ 補助関数 SplitJsonPairs / CleanJsonString が同じ
' モジュール内に必要（最小版に含まれている）
'============================================================
Sub ImportJsonFull()
'--- ★書き換えポイント ---
Dim filePath As String
filePath = "C:\temp\sample.json" '← JSONファイルのフルパス
Dim targetSheetName As String
targetSheetName = "" '← 空欄ならアクティブシート。シート名を指定も可
Dim startRow As Long
startRow = 1 '← ヘッダーを書き込む行（データは startRow+1 から）
Dim startCol As Long
startCol = 1 '← 書き込み開始列（A列=1）
'--- ★ここまで ---
On Error GoTo ErrHandler
'--- ファイル存在確認
If Dir(filePath) = "" Then
MsgBox "ファイルが見つかりません:" & vbCrLf & filePath, vbExclamation
Exit Sub
End If
'--- 対象シートを取得
Dim ws As Worksheet
If targetSheetName = "" Then
Set ws = ActiveSheet
Else
Set ws = ThisWorkbook.Worksheets(targetSheetName)
End If
'--- 既存データの上書き確認
If ws.Cells(startRow, startCol).Value <> "" Then
Dim ans As VbMsgBoxResult
ans = MsgBox("シート「" & ws.Name & "」の既存データを上書きします。" & vbCrLf & _
"よろしいですか？", vbYesNo + vbQuestion)
If ans = vbNo Then Exit Sub
End If
'--- UTF-8でファイルを読み込む
Dim stream As Object
Set stream = CreateObject("ADODB.Stream")
stream.Charset = "UTF-8"
stream.Open
stream.LoadFromFile filePath
Dim jsonText As String
jsonText = stream.ReadText(-1)
stream.Close
Set stream = Nothing
'--- BOM除去（先頭にBOMが付いている場合）
If Len(jsonText) > 0 And AscW(Left(jsonText, 1)) = 65279 Then
jsonText = Mid(jsonText, 2)
End If
'--- 改行・タブを除去
jsonText = Replace(jsonText, vbCrLf, "")
jsonText = Replace(jsonText, vbLf, "")
jsonText = Replace(jsonText, vbTab, "")
jsonText = Trim(jsonText)
'--- JSON配列かどうか確認
If Left(jsonText, 1) <> "[" Then
MsgBox "JSON配列形式（ [ で始まるデータ）ではありません。" & vbCrLf & _
"この記事のコードはフラットなJSON配列を対象としています。", vbExclamation
Exit Sub
End If
'--- 先頭・末尾の [] を除去
jsonText = Mid(jsonText, 2)
If Right(jsonText, 1) = "]" Then jsonText = Left(jsonText, Len(jsonText) - 1)
'--- 空のJSON配列チェック
If Trim(jsonText) = "" Then
MsgBox "JSONデータが空です。", vbExclamation
Exit Sub
End If
'--- オブジェクト単位に分割
Dim objSep As String
objSep = "}<<SEP>>{"
jsonText = Replace(jsonText, "},{", objSep)
Dim records() As String
records = Split(jsonText, "<<SEP>>")
'--- 高速化: 画面更新を停止
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'--- ヘッダー取得（1件目のキー名）
Dim firstRec As String
firstRec = records(0)
If Left(firstRec, 1) = "{" Then firstRec = Mid(firstRec, 2)
If Right(firstRec, 1) = "}" Then firstRec = Left(firstRec, Len(firstRec) - 1)
Dim headerPairs() As String
headerPairs = SplitJsonPairs(firstRec)
Dim keyCount As Long
keyCount = UBound(headerPairs) + 1
Dim keys() As String
ReDim keys(0 To keyCount - 1)
Dim i As Long
Dim colonPos As Long
For i = 0 To UBound(headerPairs)
colonPos = InStr(headerPairs(i), ":")
If colonPos > 0 Then
keys(i) = CleanJsonString(Left(headerPairs(i), colonPos - 1))
End If
Next i
'--- ヘッダー書き込み
Dim col As Long
For col = 0 To keyCount - 1
ws.Cells(startRow, startCol + col).Value = keys(col)
Next col
'--- ヘッダー行を太字にする
ws.Range(ws.Cells(startRow, startCol), _
ws.Cells(startRow, startCol + keyCount - 1)).Font.Bold = True
'--- データ書き込み
Dim r As Long
Dim processCount As Long
processCount = 0
For r = 0 To UBound(records)
Dim rec As String
rec = records(r)
If Left(rec, 1) = "{" Then rec = Mid(rec, 2)
If Right(rec, 1) = "}" Then rec = Left(rec, Len(rec) - 1)
Dim vals() As String
vals = SplitJsonPairs(rec)
For col = 0 To UBound(vals)
colonPos = InStr(vals(col), ":")
If colonPos > 0 Then
Dim v As String
v = CleanJsonString(Mid(vals(col), colonPos + 1))
If IsNumeric(v) Then
ws.Cells(startRow + 1 + r, startCol + col).Value = CDbl(v)
Else
ws.Cells(startRow + 1 + r, startCol + col).Value = v
End If
End If
Next col
processCount = processCount + 1
Next r
'--- 列幅を自動調整
ws.Columns.AutoFit
'--- 高速化解除
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
MsgBox processCount & " 件のデータを読み込みました。", vbInformation
Exit Sub
ErrHandler:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
On Error Resume Next
If Not stream Is Nothing Then stream.Close
Set stream = Nothing
On Error GoTo 0
MsgBox "エラーが発生しました:" & vbCrLf & _
"エラー番号: " & Err.Number & vbCrLf & _
"内容: " & Err.Description, vbCritical
End Sub
ファイルの存在確認の詳細は ファイルやフォルダの存在を確認してから処理する方法 を参照。
JSON書き出し（実務版）
Excelの表データ（1行目ヘッダー、2行目以降データ）をJSON配列形式でファイルに書き出す。
以下のコードと補助関数 EscapeJsonString をセットで同じモジュールに貼り付ける。
'============================================================
' ■ Excel → JSONファイル書き出し（実務版）
' → 表データをJSON配列形式で出力（UTF-8 BOMなし）
' → エラー処理・上書き確認付き
'============================================================
Sub ExportJsonFull()
'--- ★書き換えポイント ---
Dim outputPath As String
outputPath = "C:\temp\output.json" '← 出力先のフルパス
Dim headerRow As Long
headerRow = 1 '← ヘッダー行
Dim dataStartRow As Long
dataStartRow = 2 '← データ開始行
Dim dataStartCol As Long
dataStartCol = 1 '← データ開始列（A列=1）
'--- ★ここまで ---
On Error GoTo ErrHandler
Dim ws As Worksheet
Set ws = ActiveSheet
'--- 最終行・最終列を取得
Dim lastRow As Long
lastRow = ws.Cells(ws.Rows.Count, dataStartCol).End(xlUp).Row
Dim lastCol As Long
lastCol = ws.Cells(headerRow, ws.Columns.Count).End(xlToLeft).Column
If lastRow < dataStartRow Then
MsgBox "データがありません。", vbExclamation
Exit Sub
End If
'--- 出力先の上書き確認
If Dir(outputPath) <> "" Then
Dim ans As VbMsgBoxResult
ans = MsgBox("ファイルが既に存在します:" & vbCrLf & outputPath & vbCrLf & _
"上書きしますか？", vbYesNo + vbQuestion)
If ans = vbNo Then Exit Sub
End If
'--- ヘッダー（キー名）を取得
Dim keys() As String
ReDim keys(dataStartCol To lastCol)
Dim col As Long
For col = dataStartCol To lastCol
keys(col) = CStr(ws.Cells(headerRow, col).Value)
Next col
'--- JSON文字列を組み立て
Dim json As String
json = "[" & vbCrLf
Dim r As Long
For r = dataStartRow To lastRow
json = json & " {"
For col = dataStartCol To lastCol
'--- キー名
json = json & """" & EscapeJsonString(keys(col)) & """: "
'--- 値（型に応じて出力を変える）
Dim cellVal As Variant
cellVal = ws.Cells(r, col).Value
If IsEmpty(cellVal) Then
json = json & "null"
ElseIf IsNumeric(cellVal) And Not IsEmpty(cellVal) Then
'--- 数値はダブルクォートなし
json = json & CStr(cellVal)
Else
'--- 文字列はダブルクォート付き
json = json & """" & EscapeJsonString(CStr(cellVal)) & """"
End If
'--- 最後のカラム以外はカンマ
If col < lastCol Then json = json & ", "
Next col
json = json & "}"
'--- 最後の行以外はカンマ
If r < lastRow Then json = json & ","
json = json & vbCrLf
Next r
json = json & "]"
'--- UTF-8（BOMなし）で保存
Dim stream As Object
Set stream = CreateObject("ADODB.Stream")
stream.Type = 2 '← テキストモード
stream.Charset = "UTF-8"
stream.Open
stream.WriteText json
'--- BOMなしで保存するためにバイナリコピー
Dim binStream As Object
Set binStream = CreateObject("ADODB.Stream")
binStream.Type = 1 '← バイナリモード
binStream.Open
stream.Position = 3 '← BOM（3バイト）をスキップ
stream.CopyTo binStream
stream.Close
binStream.SaveToFile outputPath, 2 '← 2 = 上書き保存
binStream.Close
Set stream = Nothing
Set binStream = Nothing
MsgBox (lastRow - dataStartRow + 1) & " 件のデータをJSONで書き出しました。" & vbCrLf & _
outputPath, vbInformation
Exit Sub
ErrHandler:
On Error Resume Next
If Not stream Is Nothing Then stream.Close
If Not binStream Is Nothing Then binStream.Close
Set stream = Nothing
Set binStream = Nothing
On Error GoTo 0
MsgBox "エラーが発生しました:" & vbCrLf & _
"エラー番号: " & Err.Number & vbCrLf & _
"内容: " & Err.Description, vbCritical
End Sub
'============================================================
' ■ JSON文字列のエスケープ処理
' → ダブルクォート・バックスラッシュ等をエスケープ
'============================================================
Private Function EscapeJsonString(ByVal s As String) As String
s = Replace(s, "\", "\\") '← バックスラッシュを先にエスケープ
s = Replace(s, """", "\""") '← ダブルクォート
s = Replace(s, vbCrLf, "\n") '← 改行（CRLF）
s = Replace(s, vbLf, "\n") '← 改行（LF）
s = Replace(s, vbCr, "\r") '← 改行（CR）
s = Replace(s, vbTab, "\t") '← タブ
EscapeJsonString = s
End Function
書き換えポイント
| 変数 | 説明 | 初期値 |
|---|---|---|
outputPath |
出力JSONファイルのフルパス | C:\temp\output.json |
headerRow |
ヘッダー行の番号 | 1 |
dataStartRow |
データ開始行 | 2 |
dataStartCol |
データ開始列（A列=1） | 1 |
Web APIからJSONを取得して処理する場合は Webページの表をExcelに自動取得する方法 も参考になる。
—
よくある落とし穴5選
1. 64bit ExcelでScriptControlが使えない（オートメーションエラー）
自分もこれで1時間以上溶かした。ネットで「VBA JSON」と検索すると ScriptControl を使う記事がたくさん出てくる。そのまま試したら「オートメーションエラー」。Excelが64bit版だと気づくまでかなりハマった。
原因: ScriptControl は32bit COMコンポーネント。64bit Excelからは呼び出せない。
対策: この記事の自作パーサー方式を使う。64bit / 32bit どちらでも動く。
2. JSONファイルの文字コードがUTF-8でないと文字化けする
原因: VBAの Open ... For Input はShift-JIS（ANSI）として読み込む。UTF-8のJSONは文字化けする。
対策: ADODB.Stream で Charset = "UTF-8" を指定して読み込む（この記事のコードはすべてこの方法を使用）。
3. 値にダブルクォートやカンマが含まれるとパースが壊れる
原因: 単純な Split(,) ではダブルクォート内のカンマと区切りカンマを区別できない。
対策: この記事の SplitJsonPairs 関数はダブルクォート内のカンマを無視する処理を実装済み。エスケープされた \" も正しく処理する。
4. JSONファイルが見つからない（パスの指定ミス）
原因: ファイルパスの \ が足りない、ファイル名のスペルミス、ファイルが別の場所にある。
対策: Dir() でファイル存在確認を入れる。この記事のコードは存在確認を実装済み。ファイルやフォルダの存在確認の詳細は ファイルやフォルダの存在を確認してから処理する方法 を参照。
5. BOM付きUTF-8のJSONで先頭に不要文字が付く
原因: BOM（Byte Order Mark）付きUTF-8のファイルを読み込むと、先頭に見えない文字（U+FEFF）が付く。JSONパースのエラー原因になる。
対策: コード内でBOM除去処理を実装済み。AscW(Left(jsonText, 1)) = 65279 でBOMを検出し、Mid(jsonText, 2) で除去する。
—
VBAでJSONファイルが文字化けするときの対処法
「JSONを読み込んだら日本語が文字化けする」という場合、原因はファイルのエンコーディングがUTF-8でないか、読み込み時にShift-JISとして処理されている。ADODB.StreamでCharsetをUTF-8に指定して読み込めば文字化けしない。
VBAのJSONパースでデータが空になるときの対処法
「JSONファイルを読み込んだのにデータが空になる」という場合、原因はJSONの構造が想定と違うか、ファイルパスの指定ミスだ。まずDebug.Printで読み込んだテキスト全体を出力して中身を確認する。BOM付きUTF-8の場合は先頭3バイトをスキップする必要がある場合もある。
FAQ
Q1: Excelが32bitか64bitかどうやって確認する？
「ファイル」→「アカウント」→「Excelのバージョン情報」をクリックすると、ダイアログに「32ビット」または「64ビット」と表示される。自分も最初これが分からなくて調べた。
Q2: ネストが深いJSON（オブジェクトの中にオブジェクト）は扱える？
この記事のコードは「フラットなJSON配列」を対象としている。例えば {"address": {"city": "東京", "zip": "100-0001"}} のようなネストには対応していない。ネストが深い場合は再帰的なパーサーが必要になる。
Q3: Web APIのレスポンス（JSON）を直接読み込める？
XMLHTTP でAPIを叩いてレスポンス文字列を取得し、その文字列をこの記事のパーサーに渡せば処理できる。Web APIからのデータ取得は Webページの表をExcelに自動取得する方法 を参照。
Q4: CSVとJSONどちらで出力すべき？
CSVは構造がシンプルで、Excelとの相性が良い。JSONはキー名（列名）を含むのでデータの意味が明確になり、Web APIやプログラムとの連携に向いている。相手のシステムがJSON指定ならJSON、特に指定がなければCSVが手軽。CSVの取り込みは CSVファイルをExcelに正しく取り込む方法 を参照。
Q5: 数万行のJSONでも処理できる？
この記事の自作パーサーは文字列操作ベースなので、数千行程度なら数秒で処理できる。1万行を超える場合は、配列に一括読み込み→一括書き込みの高速化が効果的。実務版コードは ScreenUpdating = False で画面更新を止めて高速化している。
—
まとめ
- JSON読み込み:
ADODB.StreamでUTF-8読み込み → 自作パーサーでキー・値を抽出 → セルに展開 - JSON書き出し: セルのデータをループで読み取り → JSON文字列を組み立て → UTF-8（BOMなし）で保存
- 64bit対応: 外部ライブラリ・ScriptControlに頼らず、VBA標準機能だけで動く
- エスケープ処理: ダブルクォート・カンマ・改行を正しく処理する
SplitJsonPairs関数を使う
関連記事
- テキストファイル（txt/log）をExcelに取り込む方法 — テキスト読み込みの基本技術
- Webページの表をExcelに自動取得する方法 — Web APIからJSONを取得する応用
- CSVファイルをExcelに正しく取り込む方法 — CSV vs JSONの取込比較
- ファイルやフォルダの存在を確認してから処理する方法 — ファイル存在チェックの詳細
- ExcelデータをCSVファイルに書き出す — JSON書き出し vs CSV書き出しの使い分け
—
次にやりたくなること
- Webページの表をExcelに自動取得する方法: Web APIからJSON形式のレスポンスを取得して、そのままExcelに展開したい場合
- ExcelデータをCSVファイルに書き出す: JSON以外にCSV形式でもデータを出力したい場合
- テキストファイル（txt/log）をExcelに取り込む方法: JSON以外のテキスト形式（ログファイル等）も取り込みたい場合
コメント