access VBAでフォームから選択クエリをexcelに出力する

コンピューターのお仕事
スポンサーリンク

マイクロソフトAccessは本格的なデータベースが作れて便利ですが、時として手軽に加工ができるExcelにデータを移したい場合があります。今回は、Access VBAを使ってボタン一つでエクセルに連携させてデータを出力する仕組みを作ってみたいと思います。

スポンサーリンク

動作イメージ

Accessの商品検索フォームからエクスポートボタンを押すと、検索して絞り込んだデータが デスクトップに エクセルファイルとして出力されます。

ソースコード

ソースコードの記述場所ソースコードは、メインのプロシージャを標準モジュールに設置し、フォームのクラスオブジェクトから呼び出すようにしました。

プロジェクトウィンドウで標準モジュールが確認できない場合はメニューから「挿入」「標準モジュール」を選んでください。

ソースコードは下記のとおりです。

メインプロシージャ

Public Sub excel_out()
    Dim strac As String
    Dim varxls As Variant
    Dim WSH As Variant
    Set WSH = CreateObject("Wscript.Shell")
    
    strac = "qry_商品" 'Accessクエリを指定します。
    varxls = WSH.specialfolders("Desktop") & "\" & "商品_Export.xls"
   'デスクトップに商品_Export.xlsがない場合は作られます。
    
        DoCmd.TransferSpreadsheet acExport, _
                    acSpreadsheetTypeExcel9, strac, varxls, True
        MsgBox "データ出力は、正常に完了しました。"
        
    Set WSH = Nothing
End Sub

フォームfrm_商品のクラスオブジェクト

Private Sub cmd_clear_Click() 'クリアボタンクリック時に検索結果を初期化する
  Me.tbx_商品 = ""
  Me.Requery
End Sub

Private Sub cmd_excel_out_Click() 'フォームのボタンをクリックしたときに動作
On Error GoTo cmd_excel_out_Click_err 'エラー処理構文へ
  
  Call excel_out 'メインプロシージャ呼び出し

'エラー処理構文---------------------
cmd_excel_out_Click_exit:
  Exit Sub
cmd_excel_out_Click_err:
  MsgBox Err.Number & " " & Err.Description
  Resume cmd_excel_out_Click_exit
'----------------------------------------
End Sub

Private Sub cmd_検索_Click() '検索ボタンをクリックした時に動作
  Me.Requery
End Sub

今回使用したサンプルデータベースの概要

今回はサンプルとしてテーブル、クエリ、フォームがそれぞれ一つずつのかんたんなサンプルデータベースを作ってみました。

tbl_商品のテーブル構造

  • id (数値型,主キー)
  • 使用日 (日付型)
  • 商品名 (テキスト型)
  • 単価 (数値型)
  • 数量 (数値型)
  • 請求金額 (数値型)
  • 備考 (テキスト型)

qry_商品のSQL構文

SELECT id,使用日,商品名,単価,数量,請求金額,備考
FROM tbl_商品
WHERE (((商品名) Like "*" & [forms]![frm_商品]![tbx_商品] & "*"));

frm_商品のフォーム構造

レコードソース: qry_商品

ボタン配置

  • tbx_商品(検索窓)
  • cmd_検索(検索ボタン)
  • cmd_clear(クリアボタン)
  • cmd_excel_out(エクセル出力ボタン)

まとめ

今回はマイクロソフトAccessとエクセルを連携させたマクロを作ってみました。

データをExcel形式で出力すれば、Accessが入っていないパソコンでもデータを扱えますし、装飾や加工もかんたんです。

この記事があなたのお役に立てれば幸いです。

ご不明な点があれば、メッセージなどでお知らせください。

AccessVBAを勉強している方におすすめな書籍

コメント

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