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

VBA

Accessでデータを蓄積した後、データを加工したり、必要な情報だけを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

定数説明
acSpreadsheetTypeExcel30Microsoft Excel 3.0 形式
acSpreadsheetTypeExcel46Microsoft Excel 4.0 形式
acSpreadsheetTypeExcel55Microsoft Excel 5.0 形式
acSpreadsheetTypeExcel75Microsoft Excel 95 形式
acSpreadsheetTypeExcel88Microsoft Excel 97 形式
acSpreadsheetTypeExcel98Microsoft Excel 2000 形式
acSpreadsheetTypeExcel129Microsoft Excel 2010 形式
acSpreadsheetTypeExcel12Xml10Microsoft 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画面に追加した状態を示します。

図1 AccessからExcelファイルをエクスポートするためのコード実装例

コードの実装ができた後、VBA画面上部の「▶」アイコンをクリックして処理が適切に行われるかを確認しましょう。

実装がうまくできていると「C:¥TEST」フォルダの中に、本日の日付を「yymmdd」形式としたファイル名を持つ「xlsx」ファイルが出来上がっているはずです。

これは、図2のようにテーブルの値がExcelファイルとしてエクスポートされた結果です。

図2 AccessからExcelファイルをエクスポートした結果

まとめ

今回はAccessからExcelファイルをエクスポートする方法を紹介しました。出力する内容は、可変的にクエリを作成したり、またファイル名も任意につけることができますので、業務プロセスとして決まった出力ルーチンがある等、効率改善が図れるようなものには積極的に導入することをお勧めします。

ファイル名を可変的に設定する方法や、同じファイル名の存在有無をチェックする方法を追加した処理については、以下の記事をご参照ください。

本記事を読んでいただいた方は、業務効率の改善を推進するために日々プログラミング学習を精力的に行われていることかと思います。独学で進めることも時には必要ですが、その人にあった学習ロードマップに則り目標を達成していくことが効率よく技術を習得していく術となります。

一般的な専門学校に通って学習しようとすると、多くのコストがかかってしまいますが、目的に応じて必要な分だけ学習サービスを受けられたら良くはありませんか?

そんなあなたにお勧めな、オーダーメイド型学習支援サービスとしてCODEGYMを利用されてはいかがでしょうか。

スポンサーリンク

コメント

タイトルとURLをコピーしました