ACCESS開発事例

HOME
お役立ち情報
ACCESS開発事例
ACCESS VBA Excelエクスポート機能（VBAコード公開）

2021年5月11日 / 最終更新日時 : 2023年10月14日 hakoniwa_gadget ACCESS開発事例

ACCESS VBA Excelエクスポート機能（VBAコード公開）

ACCESSのテーブルやクエリのデータを、Excelにエクスポートして利用するシーンは多いと思います。

そんな時にACCESSの操作が分かる方であれば直接テーブルやクエリをエクスポートすればよいのですが、ACCESSに詳しくない方も使う業務データベースなら、コマンドボタン一つでエクスポートできるようにして効率を高めたいですよね。

今回は、VBAを使ってACCESSのテーブルやクエリをExcelに出力する機能をご紹介します。

こんにちは。
はこにわガジェット（@hakoniwagadget）です。

ACCESSを使った売上管理、顧客管理などのデータベース開発を行っています。
ACCESSは使いこなすために少しスキルが必要なものの、うまく活用すればExcelよりも業務の効率化が図れます。
この記事ではACCESSで実際に作成したフォームやレポートを、その作成方法と共にご紹介していきます。

1 ACCESSで作成したフォーム（完成形）
2 Docmd.TransferSpreadsheet
3 開発方針
4 VBAのプログラミング

ACCESSで作成したフォーム（完成形）

まずは完成形からお見せしましょう。
こちらは売上情報を検索するフォームです。

受注日、売上日、顧客名などで売上情報を検索することができます。
検索フォームの作成方法を知りたい方はこちらの記事を参照ください。

このフォームではACCESS上でデータの検索を行うことができますが、検索結果をExcelに出力したい、という場合があります。
例えば、売上情報をExcel上で集計分析するケースや、別のラベル印刷ソフトに顧客情報をインポートしてダイレクトメールを発信したりするケースなどです。

そのため、検索条件を保持したまま、Excelへのエクスポートを行う機能を作ってみます。
右上に「Excel出力」というボタンを作りましたので、このボタンのクリック時イベントとして作成します。

Docmd.TransferSpreadsheet

ACCESSからEXCELへエクスポートする際に使用するメソッドはDocmd.TransferSpreadsheetです。
今回もVBAコード内でこのメソッドを利用しています。

Docmd.TransferSpreadsheetは以下の構文で利用します。

Docmd.TransferSpreadsheet 変換種類 , インポート/エクスポートするファイルの種類 , インポート/エクスポートするACCESSのテーブル名 , インポート/エクスポートするファイル名 , フィールド名として最初の行を使うか否か

引数の数が多いので複雑ですが、以下のサンプルコードを見て使い方の参考としてください。

開発方針

この機能は簡単そうに見えて、実は意外と奥が深いです。
私も作るまでは、Filterプロパティに抽出条件式があるのだから、その条件式を使って簡単に出力できるだろうと思っていました。

ACCESSからExcelへエクスポートする際は、DoCmd.TransferSpreadsheetを使用するのが最も簡単ですが、こちらで出力できるのはテーブルかクエリといったACCESSオブジェクトのみです。

しかし、この検索フォームは売上データのテーブルを元に作成しており、VBAのFilterプロパティを利用してフォームで入力された条件を元に適宜テーブルにフィルターをかけて検索結果を表示しているため、検索結果のデータがテーブルやクエリとしては存在しません。

そのため、エクスポート用に一時的に、検索結果を保存するテーブルかクエリを作成する必要があるのです。
そして、エクスポートを行うたびにそのテーブルもしくはクエリを削除⇒再作成か、内容を書き換えるという処理が必要になります。

今回は、検索結果を保持するクエリを作成し、エクスポートを行うごとにクエリを削除⇒再作成、という実装方法としました。

VBAのプログラミング

コード

では、実際に作成したコードを見ていきましょう。


Dim FileName As String
Dim tmp_query As QueryDef

Private Sub Excel出力_ボタン_Click()

If vbOK = MsgBox("ACCESSファイルと同じ場所にEXCELデータで出力します。" & vbCrLf & "同名のファイルは上書きされますのでご注意ください。", vbExclamation + vbOKCancel, "売上検索結果出力") Then

    '出力用クエリの削除
    DoCmd.SetWarnings False
    On Error Resume Next
    DoCmd.DeleteObject acQuery, "EXP_売上検索"
    DoCmd.SetWarnings True

    '出力用クエリの作成
    If Me.FilterOn = False Then
    
        Set tmp_query = CurrentDb.CreateQueryDef("EXP_売上検索", "select * from list_売上;")
        
    Else
    
        Set tmp_query = CurrentDb.CreateQueryDef("EXP_売上検索", "select * from list_売上 where " & Me.Filter & ";")
    
    End If
    
    '出力処理
    FileName = Left(CurrentDb.Name, InStrRev(CurrentDb.Name, "\")) & "売上検索.xlsx"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "EXP_売上検索", FileName, True

End If

End Sub

以下、細かく解説していきます。

出力用クエリの削除

最初に出力用クエリの削除を行います。
前回操作時に残っているクエリの削除、という意味です。


    '出力用クエリの削除
    DoCmd.SetWarnings False
    On Error Resume Next
    DoCmd.DeleteObject acQuery, "EXP_売上検索"
    DoCmd.SetWarnings True

ここではDoCmd.DeleteObjectを使ってクエリを削除します。

DoCmd.DeleteObjectの前にOn Error Resume Nextを記載しているのは、該当のクエリが無い場合にエラーで止まらないようにするためです。
初回起動時はクエリが無い状態になりますので、これでエラー回避をしています。

また、DoCmd.SetWarningsを使って、アラートが出ることを防止しています。
ACCESSオブジェクトの操作をする際は、これをやっておかないと毎回アラートが出て操作性が悪くなります。

出力用クエリの作成

次に、出力用クエリを作成します。


    '出力用クエリの作成
    If Me.FilterOn = False Then
    
        Set tmp_query = CurrentDb.CreateQueryDef("EXP_売上検索", "select * from list_売上;")
        
    Else
    
        Set tmp_query = CurrentDb.CreateQueryDef("EXP_売上検索", "select * from list_売上 where " & Me.Filter & ";")
    
    End If

Set tmp_query = CurrentDb.CreateQueryDefを使ってクエリを作成します。
list_売上という検索フォームの元になっているクエリに対して、フォームのFilterプロパティの抽出条件文を適用するSQL文を作成し、EXP_売上検索というクエリを作成します。

If Me.FilterOn = False Thenで条件分岐をさせているのは、検索フォームにFilterがかかっている場合とそうでない場合でクエリを作成するためのSQL文の記載が変わるためです。
Filterがかかっていない場合は、list_売上をそのまま出力し、Filterがかかっている場合は、SQL文のWhere句に、Me.Filterの抽出条件を追記します。

出力処理

最後は作成したクエリの出力処理です。

ここまでくればあとは簡単ですね。
DoCmd.TransferSpreadsheetを使って出力を行います。


    '出力処理
    FileName = Left(CurrentDb.Name, InStrRev(CurrentDb.Name, "\")) & "売上検索.xlsx"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "EXP_売上検索", FileName, True

出力先の指定が面倒なのですが、このコードではCurrentDb.Nameから、現在使っているACCESSファイルのパスを読み出し、それをFileName変数に格納して出力先を指定しています。

ですので、ACCESSファイルと同じ場所にエクスポートされたExcelができることになります。

DoCmd.TransferSpreadsheetの引数の指定は以下の通りになっています。

引数	値
変換種類	acExport
インポート/エクスポートするファイルの種類	acSpreadsheetTypeExcel12Xml
インポート/エクスポートするACCESSのテーブル名	“EXP_売上検索”
インポート/エクスポートするファイル名	FileName
フィールド名として最初の行を使うか否か	True

インポート/エクスポートするACCESSのテーブル名、インポート/エクスポートするファイル名はテキスト型で指定する必要があるので、値を「”」（ダブルクォーテーション）で囲うことを忘れないようしてください。

今回はFileNameという変数を使ってファイル名を指定していますが、この変数をString（テキスト型）で定義しています。

以上、Excelエクスポート機能の開発事例をご紹介しました。

ACCESSを使いこなせば、業務の効率化や自動化が実現できます。
しかし、自分でACCESSを学ぶには時間がない、難しそうで不安、という方も多いでしょう。

そんな時は、ACCESS開発歴20年以上、過去に100以上のACCESSデータベースを開発してきた私（@hakoniwagadget）にお任せください。

ACCESSの新規開発、既存のACCESSの修正、ACCESSの操作レッスンなど様々なサービスをご提供しています。
ご興味のある方は下記のサービス一覧をご覧ください。