AccessとExcelを連携する方法には、大きく分けて「AccessのデータをExcelへ出力する方法」と「ExcelのデータをAccessへ取り込む方法」があります。
また、VBAを使用すれば、Excelファイルの作成や複数シートへの出力などを自動化することもできます。
目的によって適した方法が異なるため、実際の業務に応じて使い分けることが重要です。
今回は、Access VBAを用いてデータをExcelファイルとしてエクスポートする方法を紹介します。
Accessによるエクスポート処理
Excelファイルの出力は、Accessに搭載されている「Docmd.TransferSpreadsheet」を用います。ここで、出力できるデータは「テーブル」か「クエリ」となります。既存のテーブル、クエリの他、一時的に作成したクエリを用いてエクスポートすることも可能です。
ここで、一時的にクエリを作成する方法は以下の記事をご参照ください。

さて、Docmd.TransferSpreadsheetは実際にはどのように記載すれば良いでしょうか?以下から細かく見ていきましょう。まずは、基本構文を示します。
DoCmd.TransferSpreadsheet TransferType, SpreadsheetType, TableName, FileName, HasFieldNames, Range, UseOA
上で示した基本構文の内、引数はそれぞれ表1で示すことを意味しています。
表1 Docmd.TransferSpreadsheetの引数について
| 名前 | 省略 | データ型 | 説明 |
| TransferType | 可能 | AcDataTransferType | 必要な転送の種類。 既定値は acImport です。 |
| SpreadsheetType | 可能 | AcSpreadSheetType | インポート元、エクスポート先、またはリンク先のワークシートの種類を指定します。 |
| TableName | 可能 |
バリアント型 |
ワークシートのデータのインポート、エクスポート、リンクを行う Office Access テーブルの名前、または結果をワークシートにエクスポートする Access選択クエリの名前を文字列式で指定します。 |
| Filename | 可能 | バリアント型 | インポート、エクスポート、またはリンクするワークシートのファイル名およびパスを文字列式で指定します。 |
| HasFieldNames | 可能 | バリアント型 | インポートまたはリンクするときにフィールド名としてワークシートの最初の行を使用するには、True (1) を使用します。 ワークシートの最初の行を通常のデータとして扱うには、False (0) を使用します。 この引数を空白にすると、既定値 (False) と見なされます。 Access テーブルをエクスポートしたり、ワークシートへのクエリ データを選択したりすると、引数に入力する内容に関係なく、ワークシートの最初の行にフィールド名が挿入されます。 |
| 範囲 | 可能 | Variant | ワークシートのセルの範囲または範囲の名前を文字列式で指定します。 この引数はインポートにのみ適用されます。 この引数を指定しないと、ワークシート全体がインポートされます。 スプレッドシートにエクスポートする場合は、この引数を空白のままにする必要があります。 範囲を入力すると、エクスポートは失敗します。 |
ここで、SpreadsheetType引数には、出力するExcelファイルの形式を設定します。具体的には表2で示すとおりです。
表2 SpreadsheetType引数に指定するAcSpreadSheetType
| 定数 | 値 | 説明 |
| acSpreadsheetTypeExcel3 | 0 | Microsoft Excel 3.0 形式 |
| acSpreadsheetTypeExcel4 | 6 | Microsoft Excel 4.0 形式 |
| acSpreadsheetTypeExcel5 | 5 | Microsoft Excel 5.0 形式 |
| acSpreadsheetTypeExcel7 | 5 | Microsoft Excel 95 形式 |
| acSpreadsheetTypeExcel8 | 8 | Microsoft Excel 97 形式 |
| acSpreadsheetTypeExcel9 | 8 | Microsoft Excel 2000 形式 |
| acSpreadsheetTypeExcel12 | 9 | Microsoft Excel 2010 形式 |
| acSpreadsheetTypeExcel12Xml | 10 | Microsoft Excel 2010/2013/2016 XML 形式 (.xlsx、.xlsm、.xlsb) |
以上で、Docmd.TransferSpreadsheetの基本的な使い方は紹介できましたので、サンプルデータを用いてExcelへの出力の様子を見ていきましょう。
サンプルデータ
サンプルテーブルとして、「T_ExcelExport」テーブルを作成し、「氏名」、「生年月日」及び「性別」フィールドを作成します。そして、これらフィールドのデータ型は、それぞれ「短いテキスト」、「日付/時刻型」及び「短いテキスト」とします。
「T_ExcelExport」テーブルを作成後、表3に示すサンプルデータを追加します。
表3 サンプルデータ
| 氏名 | 生年月日 | 性別 |
| 坂本 花子 | 1998/01/06 | 女 |
| 山本 寛治 | 1978/04/05 | 男 |
| 森本 真一 | 1997/12/07 | 男 |
| 相川 ふみ | 2001/06/19 | 女 |
次からは、VBAによりExcelファイルへエクスポートするコードを実装していきましょう。
VBAによる実装
Access VBA画面を開き、標準モジュールを追加し、以下のコードを追加します。
Private Sub ExcelExport() '変数宣言 Dim srchXls As String 'Excelエクスポート先のファイルパス srchXls = "C:\TEST\" & Format(Date, "yymmdd") & ".xlsx" 'Excelファイルの出力 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "T_ExcelExport", srchXls, True, "出力結果" 'Excelファイルをエクスポートした旨を通知する。 MsgBox "Excelをエクスポートしました。" End Sub
追加したコードにおいて、Excelファイルの出力先を決める必要があります。ここでは、Date関数とFormat関数を用いて「本日」を「yymmdd」形式で取得し、これを「xlsx」ファイルとして出力するようにしています。なお、「C:¥TEST」としていますが、当該フォルダが存在していないとエラーになりますので、ご注意ください。
次に、TransferSpreadsheetメソッドを用いてMicrosoft Excel 2010 形式、すなわち「xlsx」形式としてエクセルファイルを当該ファイルパスにエクスポートします。ここでは、テーブルを出力対象としましたが、上述のとおりクエリを出力対象とすることもでき、またDAOを上手く組み合わせることで、適宜クエリを作成し、これをExcelファイルにエクスポートすることも可能です。
このままでは、処理が終了したことが分かりませんので、Excelファイルをエクスポートした旨を通知するためのメッセージボックスを表示させるようにしました。
図1には、上述のコードをVBA画面に追加した状態を示します。

コードの実装ができた後、VBA画面上部の「▶」アイコンをクリックして処理が適切に行われるかを確認しましょう。
実装がうまくできていると「C:¥TEST」フォルダの中に、本日の日付を「yymmdd」形式としたファイル名を持つ「xlsx」ファイルが出来上がっているはずです。
これは、図2のようにテーブルの値がExcelファイルとしてエクスポートされた結果です。

複数のExcelシートへデータを出力する場合
実務では、Accessのデータを1つのExcelシートへ出力するだけでなく、部署別・年度別・データ種別などに分けて複数のワークシートへ出力したい場合があります。
VBAを使用して複数のExcelワークシートへデータを出力する方法については、次の記事で具体的に解説しています。

既存のExcelシートへデータを転記したい場合
AccessからExcelへデータを出力する際には、新しいファイルへ出力するだけでなく、既存のExcelシートの指定した位置へ値を転記したいケースもあります。
特に、既存のExcel帳票や定型様式をそのまま利用したい場合に便利です。

VBAからExcelブック自体を作成したい場合
Access VBAからExcelを操作すると、新しいExcelブックの作成からデータの出力までを自動化することもできます。
毎月同じ形式のExcelファイルを作成する処理などを自動化したい場合に活用できます。

どのExcel出力方法を使えばよいか迷った場合
AccessからExcelへデータを出力する方法はいくつかあります。
出力するデータ量や、既存のExcelファイルを利用するか、VBAで後処理を行うかなどによって適した方法が異なります。

Excelへエクスポートできない場合
VBAからExcelへデータを出力すると、ファイルパスやExcelファイルの状態、出力先の指定などが原因で正常に処理できない場合があります。
Excelへの出力でエラーが発生する場合は、次の記事も確認してみてください。

ExcelからAccessへデータを取り込みたい場合
ここまではAccessからExcelへデータを出力する方法を紹介しましたが、反対にExcelのデータをAccessへ取り込むこともできます。
VBAを使用すれば、対象となるExcelファイルやワークシートを指定してインポート処理を自動化できます。

複数のExcelファイルをまとめて取り込みたい場合
1つのExcelファイルを取り込む方法を理解したら、次はフォルダ内に保存されている複数のExcelファイルをまとめて処理することもできます。
毎月複数の部署から届くExcelファイルをAccessへ集約する場合などに利用できます。

まとめ
今回はAccessからExcelファイルをエクスポートする方法を紹介しました。出力する内容は、可変的にクエリを作成したり、またファイル名も任意につけることができますので、業務プロセスとして決まった出力ルーチンがある等、効率改善が図れるようなものには積極的に導入することをお勧めします。
ファイル名を可変的に設定する方法や、同じファイル名の存在有無をチェックする方法を追加した処理については、以下の記事をご参照ください。

Access・Excel連携の関連記事
AccessとExcelを連携する方法は、目的によって異なります。やりたい処理に合わせて次の記事を参考にしてください。
複数のExcelシートへ出力したい
→ Access VBAでExcelの複数シートへデータを出力する方法既存のExcelシートへ転記したい
→ AccessのデータをExcelシートへ転記する方法Excelブックを自動作成したい
→ AccessからExcelブックを作成する方法どの出力方法を使えばよいか迷っている
→ AccessからExcelへデータを出力するならどの方法がおすすめ?Excelへエクスポートできない
→ AccessからExcelへエクスポートできない場合の原因と対処ExcelからAccessへ取り込みたい
→ Access VBAでExcelワークシートをインポートする方法複数Excelファイルを一括で取り込みたい
→ フォルダ内のExcelファイルをAccessへ一括インポートする方法


コメント