「マクロの記録」を卒業する。DAO Recordsetを操り、Excelへ自由自在にデータを解き放つ技術
こんにちは。今日は、Access VBAの世界へ足を踏み入れたあなたが、必ずと言っていいほど直面する「壁」を、優しく、かつ本質的に取り払っていこうと思います。
これまであなたは、`DoCmd.TransferSpreadsheet` という便利なコマンドを使って、テーブルやクエリをExcelへ出力してきたかもしれません。「ボタン一つでExcelが出る。便利だな!」……そう感じたのは、あなたが正しいからです。
しかし、エンジニアとしての階段を一段登るためには、その「魔法の杖」を一度置いて、「データの流れを自分の手で制御する」という経験が必要です。
「Excelの特定のセルにだけ色を塗りたい」
「計算結果を途中に差し込みたい」
「条件によって出力する行を変えたい」
こうした「痒いところに手が届く」制御を実現するためには、DAO(Data Access Objects)の Recordset(レコードセット) を直接、Excelのセルへと流し込む技術が不可欠です。
ここをクリアすれば、あなたのAccess VBAは「自動化ツール」から「プロフェッショナルな業務システム」へと進化します。さあ、一緒に見ていきましょう。
1. 概念の整理:Recordsetとは「データの列車」である
コードを書く前に、イメージを固めましょう。ここが曖昧だと、エラーが出た時に迷子になります。
Accessのテーブルにデータが入っている状態を「駅に止まっている列車」だと想像してください。Recordset(レコードセット) とは、その列車を走らせるための「運転士」兼「車両」です。
- Recordsetを開く: 列車を駅(テーブル)から出発させる。
- MoveNext: 次の駅(次のレコード)へ進む。
- EOF (End Of File): 終着駅に到着したことを知らせる信号。
`DoCmd.TransferSpreadsheet` は、この列車を丸ごと「箱に詰めてExcelへ投げ込む」手法です。一方、今回学ぶ手法は、「列車の各車両から荷物(データ)を取り出し、Excelという棚に一つずつ丁寧に並べていく」手法です。
自由度は格段に上がりますが、その分、列車の進み具合(ループ処理)を自分で管理しなければなりません。
2. 実践:DAO RecordsetからExcelへ一括出力するコード
それでは、実際に動くコードを見てみましょう。
ここでは、`T_商品マスタ` というテーブルからデータを読み取り、Excelへ出力するシナリオを想定します。
※事前に「Microsoft Excel 16.0 Object Library」(バージョンは環境による)への参照設定を行っておいてください。
Option Explicit
''' <summary>
''' DAOのRecordsetの内容をExcelへ直接書き出す
''' </summary>
Public Sub ExportRecordsetToExcel()
' --- 1. 変数の宣言(型を意識することがプロへの第一歩) ---
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim i As Integer
Dim r As Long ' Excelの行番号用
Dim c As Integer ' Excelの列番号用
' エラーハンドリングの設定
On Error GoTo ErrorHandler
' --- 2. オブジェクトの初期化 ---
Set db = CurrentDb
' SQL文で取得したいデータを指定(ここでは全件)
Set rs = db.OpenRecordset("SELECT * FROM T_商品マスタ", dbOpenSnapshot)
' Recordsetが空でないかチェック
If rs.EOF Then
MsgBox "出力するデータがありません。", vbExclamation
GoTo CleanUp
End If
' --- 3. Excelの起動と準備 ---
' 新しいExcelインスタンスを作成
Set xlApp = New Excel.Application
xlApp.Visible = True ' 動作確認のためExcelを表示させる
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)
' Excelの描画停止(高速化の極意:これだけで劇的に速くなります)
xlApp.ScreenUpdating = False
' --- 4. ヘッダー(項目名)の書き出し ---
' Recordsetのフィールド名をループして、Excelの1行目に書き込む
r = 1
For c = 0 To rs.Fields.Count - 1
xlSheet.Cells(r, c + 1).Value = rs.Fields(c).Name
' ヘッダーに少し装飾を施してみる(柔軟な制御の例)
xlSheet.Cells(r, c + 1).Font.Bold = True
xlSheet.Cells(r, c + 1).Interior.Color = RGB(220, 220, 220)
Next c
' --- 5. データ本体の書き出し(ループ処理) ---
r = 2 ' データは2行目から開始
' EOF(終着駅)に到達するまでループ
Do Until rs.EOF
c = 1 ' 列のカウントをリセット
' 各フィールドの値を1つずつセルへ転記
For i = 0 To rs.Fields.Count - 1
xlSheet.Cells(r, c).Value = rs.Fields(i).Value
c = c + 1
Next i
' 次のレコードへ移動(これを忘れると無限ループになります!)
rs.MoveNext
' 次の行へ
r = r + 1
Loop
' --- 6. 後処理と仕上げ ---
' 列幅の自動調整
xlSheet.Columns.AutoFit
' 描画停止を解除
xlApp.ScreenUpdating = True
MsgBox "Excelへの出力が完了しました!", vbInformation
CleanUp:
' --- 7. オブジェクトの解放(メモリ管理の肝) ---
' オブジェクトは使い終わったら必ず「Nothing」を代入して解放しましょう
On Error Resume Next
If Not rs Is Nothing Then rs.Close: Set rs = Nothing
If Not db Is Nothing Then Set db = Nothing
' Excelはユーザーに見せるため、ここでは閉じずに残します
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
Exit Sub
ErrorHandler:
' エラーが発生した場合の処理
MsgBox "エラーが発生しました。" & vbCrLf & _
"番号: " & Err.Number & vbCrLf & _
"内容: " & Err.Description, vbCritical
Resume CleanUp
End Sub
3. コードの深掘り:ここが「エンジニア」の視点です
このコードには、単なる「書き込み」以上の、プロフェッショナルな作法が詰まっています。
① `xlApp.ScreenUpdating = False` の魔法
Excelは、セルに値を書き込むたびに「画面を書き換えよう」とします。大量のデータがある場合、この「画面更新」のコストが膨大になり、処理が極端に遅くなります。
最初に `False` にして、最後に `True` に戻す。これだけで、処理速度は数倍から数十倍に跳ね上がります。
② `Do Until rs.EOF` と `rs.MoveNext`
これがRecordset操作の心臓部です。
`rs.EOF` は「これ以上データがない」という状態を指します。この条件を満たすまでループを回し、ループの最後で必ず `rs.MoveNext` を呼んで「次の駅」へ進ませる。
もし `MoveNext` を忘れると、プログラムは同じ駅(レコード)を永遠に回り続け、Accessがフリーズします。
③ オブジェクトのライフサイクル管理 (`Set … = Nothing`)
VBAにおいて、最も恐ろしいのは「メモリリーク」です。
ExcelやRecordsetといった大きなオブジェクトを使い終わった後に適切に解放(Nothingを代入)しないと、目に見えないところでExcelのプロセスが大量に残ってしまい、PCの動作を重くします。`CleanUp` セクションで丁寧に片付ける癖をつけましょう。
4. 陥りやすいエラーと対策
初心者が必ずと言っていいほどハマるポイントをまとめておきます。
| エラーの症状 | 原因 | 対策 |
|---|---|---|
| Accessが固まる(無限ループ) | `rs.MoveNext` の書き忘れ | ループの最後で必ず次へ進める。 |
| 「型が一致しません」エラー | Null値(空っぽのデータ)の扱い | `rs.Fields(i).Value & “”` のように空文字を結合して回避するか、`IsNull()` 関数で判定する。 |
| Excelが裏で残り続ける | オブジェクトの解放漏れ | `CleanUp` 処理を必ず通るように設計する。 |
| 実行速度が異常に遅い | 画面更新がONのまま | `ScreenUpdating = False` を活用する。 |
最後に:一歩ずつ、確実に
いかがでしたでしょうか?
`DoCmd.TransferSpreadsheet` を使うよりも、コード量は増え、覚えることも多くなりました。しかし、この「自分でコントロールする感覚」こそが、プログラミングの醍醐味です。
まずは、提示したコードをコピーして、自分の環境のテーブル名に書き換えて動かしてみてください。動いた瞬間の感動が、あなたを真のエンジニアへと導いてくれます。
ここをクリアすれば、Access VBAの基本はバッチリですよ。
次は、さらに高度な「条件付き書式」や「複数シートへの出力」にも挑戦していきましょう。応援しています!
