「DoCmd.TransferSpreadsheet」を卒業せよ:DAO RecordsetをExcelへ高速・高精度に解き放つ「配列バッファ」設計術
業務自動化の現場において、AccessからExcelへのデータ出力は避けて通れない儀式だ。
多くの開発者は、手軽に実装できる `DoCmd.TransferSpreadsheet` を選択する。確かに、それは「動く」コードだ。しかし、実務における要件は、単なるデータの垂れ流しでは済まない。「特定のセルに色を付けろ」「ヘッダーの書式を整えろ」「特定の列だけ計算式を挿入しろ」「結合セルを使いこなせ」……。
`TransferSpreadsheet` はブラックボックスだ。中身を制御することはできない。
一方で、初心者によく見られる「Recordsetをループさせて、`Cells(i, j).Value = …` と一つずつセルに書き込む」という手法は、最悪の選択だ。これは、通信コスト(COMオーバーヘッド)を無視した、設計思想の欠如したコードと言わざるを得ない。
本稿では、プロフェッショナルが採用すべき、「DAO RecordsetからVariant配列を経由した一括書き込み」というアーキテクチャを伝授する。これこそが、速度、柔軟性、そして堅牢性を両立させる唯一の解である。
1. なぜ「セルへの直接書き込み」は敗北するのか
まず、設計における「重み」を理解してほしい。
VBAからExcelを操作する場合、AccessのプロセスとExcelのプロセスは別物だ。`Cells(i, j).Value = …` を実行するたびに、VBAはExcelに対して「このセルにこの値を置いてくれ」というリクエストを投げ、Excelはそれに応答する。この「プロセス間の通信(COM呼び出し)」のコストは、極めて高い。
1,000行×10列のデータがあれば、10,000回の通信が発生する。これが10万行になったとき、君のツールはフリーズしたかのような挙動を見せるだろう。
プロの設計思想はこうだ:
「Excelへの通信は、最小限に抑えよ。メモリ上のバッファ(配列)で全ての準備を整え、最後の一撃でExcelへ叩き込め。」
2. 理想的なアーキテクチャ:Recordset $\rightarrow$ Variant配列 $\rightarrow$ Excel Range
我々が構築すべきは、以下の3ステップのパイプラインだ。
1. Extract (抽出): DAO Recordsetからデータを読み取る。
2. Transform (変換): 読み取ったデータを、メモリ上の「Variant型二次元配列」に格納する。
3. Load (展開): 完成した配列を、Excelの特定のRangeに対して一括で代入する。
この手法により、通信回数は「データ件数 $\times$ 列数」から、実質的に「1回」へと劇的に削減される。
3. プロダクション・クオリティのコード実装
以下に、そのまま実務のクラスモジュールや標準モジュールに組み込める、堅牢な実装例を示す。単なる書き込みだけでなく、エラーハンドリングとオブジェクトのライフサイクル管理(解放)を徹底している。
Option Compare Database
Option Explicit
''' <summary>
''' DAO RecordsetのデータをExcelへ高速かつ柔軟に出力する
''' </summary>
''' <param name="rs">出力元となるDAO.Recordset</param>
''' <param name="targetSheet">出力先のExcel.Worksheetオブジェクト</param>
''' <param name="startCell">出力開始セル (例: "A1")</param>
Public Sub ExportRecordsetToExcel(ByRef rs As DAO.Recordset, _
ByRef targetSheet As Object, _
ByVal startCell As String)
' --- 1. 前提条件のバリデーション ---
If rs Is Nothing Then
Err.Raise vbErr_ObjectRequired, "ExportRecordsetToExcel", "RecordsetがNothingです。"
End If
If rs.EOF And rs.BOF Then
MsgBox "出力対象のデータが存在しません。", vbInformation
Exit Sub
End If
On Error GoTo ErrorHandler
Dim i As Long, j As Long
Dim rowCount As Long
Dim colCount As Long
Dim dataArray() As Variant ' メモリ上のバッファ(二次元配列)
' --- 2. データの次元(行数・列数)を特定 ---
' ※ rs.MoveLastが必要な場合があるが、パフォーマンスを考慮し、
' 既にカーソルが末尾にいない場合は、RecordCountを確認する設計にする
rs.MoveLast
rowCount = rs.RecordCount
rs.MoveFirst
colCount = rs.Fields.Count
' --- 3. 配列のメモリ確保 ---
' 配列の添字は 1 から始める(Excelのセル範囲と整合性を取るため)
ReDim dataArray(1 To rowCount, 1 To colCount)
' --- 4. Extract & Transform: Recordsetから配列への転送 ---
' ここでのループはメモリ内(Accessプロセス内)で行われるため、極めて高速
i = 1
Do Until rs.EOF
For j = 1 To colCount
' Null値によるエラーを避けるため、Variant型としてそのまま格納
dataArray(i, j) = rs.Fields(j - 1).Value
Next j
i = i + 1
rs.MoveNext
Loop
' --- 5. Load: Excelへの一括書き込み ---
' ExcelのRangeオブジェクトに対して、配列を一気に流し込む
' これにより、通信回数が「1回」に集約される
Dim targetRange As Object
Set targetRange = targetSheet.Range(startCell).Resize(rowCount, colCount)
targetRange.Value = dataArray
' --- 6. レイアウト制御(ここが TransferSpreadsheet にない柔軟性) ---
' ここに書式設定のロジックを記述できる
With targetRange
.Font.Name = "游ゴシック"
.Font.Size = 10
' ヘッダー行の装飾例(1行目)
With .Rows(1)
.Interior.Color = RGB(220, 230, 241)
.Font.Bold = True
End With
End With
MsgBox "出力が完了しました。", vbInformation
CleanUp:
' オブジェクトのライフサイクルを管理し、メモリリークを防ぐ
Set targetRange = Nothing
Exit Sub
ErrorHandler:
MsgErr "ExportRecordsetToExcelでエラーが発生しました。" & vbCrLf & _
"Error #" & Err.Number & ": " & Err.Description
Resume CleanUp
End Sub
' 補助関数:エラーメッセージの整形
Private Sub ErrMsg(ByVal msg As String)
MsgBox msg, vbCritical, "Runtime Error"
End Sub
4. 運用における「極限の知見」
このコードを使いこなすために、以下の3点を肝に銘じておいてほしい。
① Null値への耐性
`rs.Fields(j).Value` を直接セルに書き込む際、`String` 型などのプリミティブ型に代入しようとすると、データベースの `Null` が衝突してエラーになる。必ず受け皿となる変数は `Variant` 型とし、配列も `Variant` で宣言すること。これが、バグの起きない堅牢な設計の基本だ。
② メモリ管理の責務
`rs.MoveLast` は、レコードセットの全件数を把握するために必要だが、巨大なデータセット(数百万件規模)では、これ自体がコストになる。もし、件数が事前にわかっている、あるいは「最初の1000件だけで良い」という要件なら、`MoveLast` は避けるべきだ。
③ Excelの「Range.Resize」の魔力
コード内の `.Resize(rowCount, colCount)` は、非常に強力なテクニックだ。開始セルから「何行・何列分か」を動的に決定することで、配列のサイズとExcelの書き込み範囲を完璧に一致させることができる。これが、ズレのない正確な出力を保証する。
結論
`DoCmd.TransferSpreadsheet` は、プロトタイプを作るための道具だ。
しかし、実務で「誰かに使われるツール」を作るのであれば、データと表示の間に「配列という緩衝材」を置くアーキテクチャを構築せよ。
速度、柔軟性、そして保守性。これらを手に入れた時、君の書くコードは単なるスクリプトから、真の「業務アプリケーション」へと昇華する。
