PR

簡単便利!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

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

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

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

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

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

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

まとめ

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

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

今の時代、VBAに限らずプログラミングができるということは、当然のスキルとして広く社会で認知されています。いまだプログラミングが十分にできないという人は、今からでも遅くありません。できるだけ早く基本的なプログラミングスキルを習得することをお勧めします。

以下で紹介している書籍を参考に、実践的なプログラミング・データベース・サーバ、データ分析・機械学習など、システムエンジニアや今後を担うDX人材に必要とされているスキル獲得に向けて基礎から学んでいくことが大切です。

最初につまずきがちな点も、書籍を読みながら試行錯誤して課題解決していくことで、自身のスキルアップを目指すことができます。思い立ったが吉日、是非業務効率の改善に向けてプログラミング学習に勤しんでください!

スポンサーリンク

Kindle Unlimitedにサインアップして無料体験に登録する
Kindle Unlimitedに登録すると、人気のシリーズ、ベストセラー、名作などに好きなだけアクセスして、シームレスなデジタル読書体験を実現できます。

コメント

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