マイクロソフト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が入っていないパソコンでもデータを扱えますし、装飾や加工もかんたんです。
この記事があなたのお役に立てれば幸いです。
ご不明な点があれば、メッセージなどでお知らせください。
コメント