トップ > VBA・VBS > Access VBA > テーブル・クエリの結果をExcelへ一括書き出す

テーブル・クエリの結果をExcelへ一括書き出す

Access VBA(オブジェクト名リストでまとめて .xlsx 化)

Accessで集計した結果を、Excelで渡してほしいと言われる。オブジェクトを1つずつ右クリックしてエクスポートするのは、数が多いと面倒です。このレシピでは、書き出したいテーブル・クエリの名前をリストに書いておき、まとめてExcelファイル化します。ファイル名はオブジェクト名そのままなので、受け取る側も分かりやすい形になります。

✅ これができます:テーブル・クエリの内容を、まとめてExcelファイルとして書き出せるようになります

完成形(実行前 → 実行後)

実行前
実行前
実行後
実行後

実行すると、リストのオブジェクトが1つずつExcelファイルになってフォルダに並びます

これをコピペすれば動きます

Option Explicit
 
' ===== 設定エリア:変えるのはここだけ =====
' ① 書き出すテーブル・クエリの名前(; で区切って並べます)
Const OBJECT_LIST As String = "T_顧客マスタ; Q_月別売上"
' ② 書き出し先フォルダ
Const OUT_FOLDER As String = "C:\仕事\書き出し"
' ===== ここから下は変えなくてOK =====
 
Sub テーブルとクエリをExcelへ書き出す()
Dim names() As String
Dim i As Long, ok As Long, ng As Long
Dim nm As String, fname As String
 
If Dir(OUT_FOLDER, vbDirectory) = "" Then
MsgBox "書き出し先フォルダが見つかりません:" & OUT_FOLDER, vbExclamation, "Excelへの書き出し"
Exit Sub
End If
If Trim(OBJECT_LIST) = "" Then
MsgBox "設定エリア①のリストが空です", vbExclamation, "Excelへの書き出し"
Exit Sub
End If
 
names = Split(OBJECT_LIST, ";")
ok = 0
ng = 0
For i = LBound(names) To UBound(names)
nm = Trim(names(i))
If nm <> "" Then
fname = OUT_FOLDER
If Right(fname, 1) <> "\" Then
fname = fname & "\"
End If
fname = fname & nm & ".xlsx"
On Error Resume Next
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, nm, fname, True
If Err.Number <> 0 Then
ng = ng + 1
Err.Clear
Else
ok = ok + 1
End If
On Error GoTo 0
End If
Next i
MsgBox "書き出しが終わりました(成功:" & ok & " 件、失敗:" & ng & " 件)。", vbInformation, "Excelへの書き出し"
End Sub

コードの黄色い部分が「設定エリア」です。変えるのは、原則ここだけです。

変えるのはここだけ

変える場所を示した図
変えるのは設定エリアの2か所だけ。番号は下の表と対応しています
番号変える場所意味書き方・例
1OBJECT_LIST書き出すテーブル・クエリの名前リストテーブル名でもクエリ名でもOKです。; (半角セミコロン)で区切って並べます
2OUT_FOLDER書き出し先フォルダ先にフォルダを作っておき、その場所を書きます。例:"C:\仕事\書き出し"

組み込み手順

  1. 書き出し先フォルダを先に作っておきます
  2. Accessで Alt+F11 →「挿入」→「標準モジュール」でモジュールを追加します
  3. このページのコードを貼り付け、設定エリア①②を書き換えます
  4. まずはリストを1件だけにして試し実行し、Excelファイルが開けることを確認します
  5. 問題なければリストを本番分に広げて実行します

⚠ VBAエディタの開き方:Alt+F11 で開きます。「開発」タブが出ていない場合は、ファイル→オプション→リボンのユーザー設定で「開発」にチェックを入れると出ます。

⚠ 保存場所と注意:AccessのVBAは、データベースファイル(.accdb)の中に保存されます。ファイルごとバックアップを取っておくと、マクロもデータもまとめて守れます。実行クエリの結果は元に戻せないため、処理の前には必ずファイルのコピーを取ってください。

⚠ セキュリティ警告バー:開いたときに黄色いバーが出たら「コンテンツの有効化」を押してください。押すまではマクロは動きません。

⚠ ファイルのブロック解除:ネットから入手したファイルは、開く前に「右クリック→プロパティ→『許可する』にチェック」が先です。これをしないと、有効化のボタン自体が出ないことがあります。

⚠ 会社の設定で無効化されている場合:警告バーも出ずに無反応なときは、会社の設定でマクロが止められている可能性が高いです。設定を突破しようとせず、情シスに「業務でマクロを使いたい」と相談するのが正解です。

動かし方

1オブジェクト=1ファイル(.xlsx)で書き出します。1行目にはフィールド名が入ります。同名のExcelファイルが既にある場合は、内容が差し替わることがあるため、残したいファイルは先に移動してください。クエリは、書き出し時点の結果(最新の集計結果)が出力されます。

カスタマイズ例

ファイル名に日付を付けたい

fname の組み立てで、nm の前に Format(Date, "yyyymmdd_") をつなげると、日付入りのファイル名になります。

1つのExcelに複数シートでまとめたい

書き出し先ファイルを同じ名前にすると、オブジェクトごとにシートが分かれる形にもできます。ただし既存シートとの兼ね合いがあるため、まずは1ファイルずつの運用が確実です。

つまずきどころ

関連レシピ

次はこれ:レポートをPDFで一括出力する
Excelへの書き出しの次は、レポートのPDF化です

よくある質問

Q. 右クリックのエクスポートと何が違う?

A. 結果は同じです。このレシピは、複数オブジェクトをリストでまとめて流せる点と、定型の月次書き出しを繰り返し使える点が違いです。

Q. .xls(旧形式)で書き出せる?

A. acSpreadsheetTypeExcel12Xml の部分を旧形式の定数に変えると可能ですが、現在のExcelなら .xlsx で問題ありません。

Q. 書き出したファイルをそのまま集計に使える?

A. はい。値とフィールド名が入った表として書き出されるので、そのままExcelの表として使えます。

PR(広告)

もっと体系的に学びたい人へ
自動化の幅を広げるなら、講座で体系化するのが近道です。

Udemy:Excelショートカット講座を見る