PR

AccessからExcelへデータを出力する方法|VBAで自動化

VBA

AccessとExcelを連携する方法には、大きく分けて「AccessのデータをExcelへ出力する方法」と「ExcelのデータをAccessへ取り込む方法」があります。

また、VBAを使用すれば、Excelファイルの作成や複数シートへの出力などを自動化することもできます。

目的によって適した方法が異なるため、実際の業務に応じて使い分けることが重要です。

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

スポンサーリンク

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

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

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

Access DAOを用いて選択クエリを作成する方法
DAOを用いて選択クエリを作成する方法を紹介します。特に、WHERE条件に応じて選択クエリを可変的に作成する場合に効果的です。

さて、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ファイルをエクスポートした結果

複数のExcelシートへデータを出力する場合

実務では、Accessのデータを1つのExcelシートへ出力するだけでなく、部署別・年度別・データ種別などに分けて複数のワークシートへ出力したい場合があります。

VBAを使用して複数のExcelワークシートへデータを出力する方法については、次の記事で具体的に解説しています。

Access VBA Excelの複数のシートにデータをエクスポートする方法
今回は、Accessデータベースに保存されているデータを項目別にExcelのシート単位でエクスポートする方法を紹介します。

既存のExcelシートへデータを転記したい場合

AccessからExcelへデータを出力する際には、新しいファイルへ出力するだけでなく、既存のExcelシートの指定した位置へ値を転記したいケースもあります。

特に、既存のExcel帳票や定型様式をそのまま利用したい場合に便利です。

便利機能!!AccessのデータをExcelシートに転記する方法
Excelで作成されている定型フォーマットにAccessデータを転記する方法を紹介します。

VBAからExcelブック自体を作成したい場合

Access VBAからExcelを操作すると、新しいExcelブックの作成からデータの出力までを自動化することもできます。

毎月同じ形式のExcelファイルを作成する処理などを自動化したい場合に活用できます。

業務効率化に向けて!AccessからExcelブックを作成する方法
今回は、AccessからExcelファイルを作成する方法を紹介します。比較的簡単なコードで実装することができ、またAccessからExcelへのデータ移行処理を実装することで業務効率を大幅に改善することが期待できますので、是非試してみてはいかがでしょうか。

どのExcel出力方法を使えばよいか迷った場合

AccessからExcelへデータを出力する方法はいくつかあります。

出力するデータ量や、既存のExcelファイルを利用するか、VBAで後処理を行うかなどによって適した方法が異なります。

AccessからExcelへデータを出力するならどの方法がおすすめ?3つのエクスポート方法を比較してみた
AccessからExcelへデータを出力する3つの方法を比較します。標準機能、VBA、Copilot活用それぞれのメリット・デメリットを解説し、実務でおすすめのエクスポート方法を紹介します。

Excelへエクスポートできない場合

VBAからExcelへデータを出力すると、ファイルパスやExcelファイルの状態、出力先の指定などが原因で正常に処理できない場合があります。

Excelへの出力でエラーが発生する場合は、次の記事も確認してみてください。

AccessからExcelへエクスポートできない!VBAでよくあるエラーと対処法を徹底解説
Access VBAでExcelへエクスポートする際によく発生するエラーと対処法を解説します。ファイルアクセスエラー、パスエラー、権限エラー、処理速度低下など実務で遭遇しやすいトラブルへの対応方法を紹介します。

ExcelからAccessへデータを取り込みたい場合

ここまではAccessからExcelへデータを出力する方法を紹介しましたが、反対にExcelのデータをAccessへ取り込むこともできます。

VBAを使用すれば、対象となるExcelファイルやワークシートを指定してインポート処理を自動化できます。

Access VBA 任意のExcelワークシートをインポートする方法
Excelファイル中の任意のワークシートからAccessへデータインポートする方法を紹介します。

複数のExcelファイルをまとめて取り込みたい場合

1つのExcelファイルを取り込む方法を理解したら、次はフォルダ内に保存されている複数のExcelファイルをまとめて処理することもできます。

毎月複数の部署から届くExcelファイルをAccessへ集約する場合などに利用できます。

超絶簡単!フォルダ内の全てのExcelデータをAccessにインポートする方法
多数あるExcelファイルのデータを一括でAccessへインポートする方法について紹介します。大変便利なので、本記事を参考にしていただければと思います。

まとめ

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

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

Accessによる在庫管理 その11 在庫情報のExcel出力
データベースにデータが蓄積されてくると、Excel等のファイルに出力して確認したいといったニーズが出てきます。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へ一括インポートする方法

コメント

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