簡単便利！Access VBAでExcelファイルにエクスポートする方法

Accessでデータを蓄積した後、データを加工したり、必要な情報だけをExcelへ出力するといったニーズがあります。

今回は、Access VBAを用いてデータをExcelファイルとしてエクスポートする方法を紹介します。

Accessによるエクスポート処理
サンプルデータ
VBAによる実装
まとめ

Accessによるエクスポート処理

Excelファイルの出力は、Accessに搭載されている「Docmd.TransferSpreadsheet」を用います。ここで、出力できるデータは「テーブル」か「クエリ」となります。既存のテーブル、クエリの他、一時的に作成したクエリを用いてエクスポートすることも可能です。

ここで、一時的にクエリを作成する方法は以下の記事をご参照ください。

さて、Docmd.TransferSpreadsheetは実際にはどのように記載すれば良いでしょうか？以下から細かく見ていきましょう。まずは、基本構文を示します。

DoCmd.TransferSpreadsheet TransferType, SpreadsheetType, TableName, FileName, HasFieldNames, Range, UseOA

上で示した基本構文の内、引数はそれぞれ表１で示すことを意味しています。

表1　Docmd.TransferSpreadsheetの引数について

名前	省略	データ型	説明
TransferType	可能	AcDataTransferType	必要な転送の種類。既定値は acImport です。
SpreadsheetType	可能	AcSpreadSheetType	インポート元、エクスポート先、またはリンク先のワークシートの種類を指定します。
TableName	可能	バリアント型	ワークシートのデータのインポート、エクスポート、リンクを行う Office Access テーブルの名前、または結果をワークシートにエクスポートする Access選択クエリの名前を文字列式で指定します。
Filename	可能	バリアント型	インポート、エクスポート、またはリンクするワークシートのファイル名およびパスを文字列式で指定します。
HasFieldNames	可能	バリアント型	インポートまたはリンクするときにフィールド名としてワークシートの最初の行を使用するには、True (1) を使用します。ワークシートの最初の行を通常のデータとして扱うには、False (0) を使用します。この引数を空白にすると、既定値 (False) と見なされます。 Access テーブルをエクスポートしたり、ワークシートへのクエリデータを選択したりすると、引数に入力する内容に関係なく、ワークシートの最初の行にフィールド名が挿入されます。
範囲	可能	Variant	ワークシートのセルの範囲または範囲の名前を文字列式で指定します。この引数はインポートにのみ適用されます。この引数を指定しないと、ワークシート全体がインポートされます。スプレッドシートにエクスポートする場合は、この引数を空白のままにする必要があります。範囲を入力すると、エクスポートは失敗します。

ここで、SpreadsheetType引数には、出力するExcelファイルの形式を設定します。具体的には表２で示すとおりです。

表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画面に追加した状態を示します。