Access クエリを実行して出力するマクロ

質問

環境
WindowsXP、Access2010

質問
以下のような作業をマクロで自動化したいと考えています。
マクロをどのように設定すればよろしいでしょうか？
お手数かけて恐れ入りますがご教示いただければ幸いです。

■使用するテーブル
ID|名前|出身地
--------------
1|大田|東京都
2|川崎|神奈川
3|足立|東京都

■自動化したい手順
1. 出身地が"東京都"のレコードのみを抽出する
2. ↑のデータを 東京都.xls のファイル名でエクスポート
3. 出身地が"神奈川"のレコードのみを抽出する
4. ↑のデータを 神奈川.xls のファイル名でエクスポート
5. 終わり

■東京都.xls のエクスポート結果
ID|名前|出身地
--------------
1|大田|東京都
3|足立|東京都

■神奈川.xls のエクスポート結果
ID|名前|出身地
--------------
2|川崎|神奈川

■補足
・出身地ごとにクエリを作成すれば同様の事が実現できますが、
　それだと、作成するクエリ数が多くなるので、できれば避けたいです。
・VBAの知識はほとんどありませんが、
　教えていただければ自分で調べながら試してみます。

なにとぞよろしくお願いいたします。

回答No.5

mitarashi · ベストアンサー

#1～3です。
db.QueryDefs.Delete "Q_temp"は書き出し都度実行していますので、正常終了すれば残らない筈です。
ご指摘の内容は、当方では再現されません。何かエラーが出ていないでしょうか。

標準モジュールにおいて実行しても良いですし、フォームから使用されるなら、フォームモジュールに置いても動くと思います。

マクロから標準モジュールのプロシージャを実行するには、Sub ではダメでFunctionにする必要があります。普通Functionは戻り値を戻しますが、今回の例では単純にSub→Functionに書き換えるだけで良いと思います。
マクロのアクションにプロシージャの実行を選択し、プロシージャ名として、test()と、括弧もつけて設定します。

以上、ご参考まで。

お礼

たびたびありがとうございます！

エラーは、実行失敗したときのQ_tempがそのまま残ってしまっていたのが原因でした。
ナビゲーションウィンドウからQ_tempを手動で消したら直りました。
お騒がせしました。

丁寧に説明していただいたおかげで、マクロから実行させることができました。
とても助かりました！

回答No.4

layy

マクロにしてモジュール形式で保存するとＶＢＡになります。

マクロではプロシージャ実行にします。

クエリはＳＱＬ記述にしてＶＢＡ側でそのＳＱＬを実行させることと同意です。

ＶＢＡ側なら条件は変数化できるので、一部違うだけの複数のクエリでもＳＱＬ文は使い回しできます。

こんなところを調べて
ＶＢＡでそのクエリのＳＱＬを実行させることを考えたらいいでしょう。

お礼

お礼が遅れまして申し訳ございません。

ご回答を参考にさせていただきながら、
しばらく試行錯誤してみます。

ありがとうございました！

回答No.3

mitarashi

#1,#2です。また出てくる羽目になりました。
確認不足で申し訳ありませんが、ご質問に、xls形式と書いてありますね。
その場合は、下記の通り変更してください。
destFileName = "C:\" & RS(0) & ".xls"
If Dir(destFileName) <> "" Then Kill destFileName
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Q_temp", destFileName, True, ""

acSpreadsheetTypeExcel9のところは、DoCmdのところから打ち込むと、多数のバージョンに対応した候補が表示されますので、お好きなものを選択してください。当方で常用しているExcel2000だと、8に相当します。

お礼

mitarashiさん

ありがとうございます！
ひとまずやりたい事にだいぶ近づくことができました。

重ねて質問させていただきたいのですが・・・

・再実行するとエラー3012が出てしまいます。
　修正方法としては↓のように書けばいいのでしょうか？
　DoCmd.DeleteObject acQuery, "Q_temp"

・こちらのコードは標準モジュールで書けばいいのですか？
　それともクラスモジュールに書けばいいのでしょうか？

・マクロから呼び出して実行させたいのですが、
　いろいろ試してみましたがうまくいきません。
　どのように設定すればよろしいでしょうか？

お手数かけてしまい申し訳ありませんが
ご指導いただけますとたいへん助かります。
なにとぞよろしくお願いいたします。

回答No.2

mitarashi

#1です。Access2010でやってみました。（こんな機会がないと使わないもので）
前回より、少し改良してあります。
Table2は、実際のテーブル名に変更してください。ご参考まで。
Sub test()
Dim db As DAO.Database
Dim qdf As QueryDef
Dim RS As DAO.Recordset
Dim mySQL As String, destFileName As String

'重複しない出身地リストを作成
Set db = CurrentDb
mySQL = "SELECT DISTINCT 出身地 FROM Table2;"
'会社名リストの各レコードについて処理
Set RS = db.OpenRecordset(mySQL)
RS.MoveFirst
Do Until RS.EOF
mySQL = "SELECT * FROM Table2 WHERE 出身地='" & RS(0) & "';"
'テンポラリクエリを作成
Set qdf = db.CreateQueryDef("Q_temp", mySQL)
DoCmd.SetWarnings False
'出力先エクセルファイルがすでに存在していれば削除
destFileName = "C:\" & RS(0) & ".xlsx"
If Dir(destFileName) <> "" Then Kill destFileName
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Q_temp", destFileName, True, ""
db.QueryDefs.Delete "Q_temp"
DoCmd.SetWarnings True
Set qdf = Nothing
RS.MoveNext
Loop
RS.Clone
Set RS = Nothing
db.Close: Set db = Nothing
End Sub

回答No.1

mitarashi

よく似た案件に回答しております。ご参考まで。
http://okwave.jp/qa/q6043020.html