アクセスからVBAでエクセルに出力する方法

質問

アクセスからVBAでエクセルに出力する方法
アクセスで下記のようなクエリの結果に対して、VBAにて会社毎のエクセルファイルを作成、
保存したいと思いますが、どのようにすればいいでしょうか？

ファイル名は、[KAISYA_MEI].xlsとし、それぞれをC:\ などに保存したい。

ちなみにExcel2003、Access2003を使用しています。

・アクセスのクエリ結果
KAISYA_ID 　　KAISYA_MEI　　TANTOSYA_MEI　　　　　MAIL　　　　　TEL
1　　　　　　　　○○社　　　　　　　　＊＊＊　　　　　　　　***@*** 0*-***-****
2　　　　　　　　△△社　　　　　　　　＊＊＊　　　　　　　　***@*** 0*-***-****
2　　　　　　　　△△社　　　　　　　　＊＊＊　　　　　　　　***@*** 0*-***-****
3　　　　　　　　□□社　　　　　　　　＊＊＊　　　　　　　　***@*** 0*-***-****
3　　　　　　　　□□社　　　　　　　　＊＊＊　　　　　　　　***@*** 0*-***-****

↓　
ファイル名：○○社.xls のエクセルファイル
KAISYA_ID 　　KAISYA_MEI　　TANTOSYA_MEI　　　　　MAIL　　　　　TEL
1　　　　　　　　○○社　　　　　　　　＊＊＊　　　　　　　　***@*** 0*-***-****

↓　
ファイル名：△△社.xlsのエクセルファイル
KAISYA_ID 　　KAISYA_MEI　　TANTOSYA_MEI　　　　　MAIL　　　　　TEL
2　　　　　　　　△△社　　　　　　　　＊＊＊　　　　　　　　***@*** 0*-***-****
2　　　　　　　　△△社　　　　　　　　＊＊＊　　　　　　　　***@*** 0*-***-****

...

よろしくお願い致します。

回答No.3

mitarashi · ベストアンサー

もっぱらADOを使っておりますが、本課題はDAOの方が楽だと考え、諸サイトを参考に作成してみましたが、#2のリンク先で、DAOを用いているコードとほぼ同じですね。一時的なクエリを作成している（事前にダミーのクエリを作っておく必要なし）のが違っているのと、一時的なテーブルを作成していますが、これはdb.OpenRecordsetに直接SQLを与えてRecordsetを取得すれば必要がない様です。折角作ったので、ご参考までに載せておきます。テーブル名の記載がなかったので、「Table名」という名前のテーブルで記述しています。
'DAOに参照設定要
Sub test()
Dim db As DAO.Database
Dim qdf As QueryDef
Dim RS As DAO.Recordset
Dim mySQL As String, destFileName As String
Dim i As Integer

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

お礼

ご教示頂いたコードを参照させて頂いた結果、実行できました！困っておりましたので、大変助かりました。本当に有り難うございました。

回答No.2

30246kiku

最近似たような質問をみかけました。

フィールド名毎にCSV出力したいです
http://www.moug.net/faq/viewtopic.php?t=51697

上記は、CSVへの出力になりますが、
DoCmd.TransferText
部分をExcel出力用に置き換えることで、できると思います。

お礼

リンク先を確認させて頂きましたが、ご指摘のように似た質問があったのですね。大変有り難うございました。

回答No.1

imogasi

色んな考えがあると思うが、
まず方式の決定が必要。
（１）クエリを会社ごとに実行（いわばパラメータクエリのパラメータが会社名）
それをマクロなどで、エクスポートコマンドでエクセルのファイル・シートにエクスポートするのが早いいのでは。
ただDistinctな会社名ファイルが必要になるが。
またエクセルにはMSクエリというアクセスまがいの機能もある。
（２）または、アクセス側でクエリの結果を、会社名でソートして、レコードとして読んで、エクセルVBAでエクセルファイルのシートに書き、会社名が途切れたときに別ファイルに移るほうが良いかもしれない。
（３）一旦エクセルの、あるブックのシートに、全データをエクスポートで集め、会社名でソートして、エクセルVBAで会社ごとに別ブックに振り分ける、のも単純で良いかもしれない。
ーー
相当手馴れていないと上記をVBAでやるのは難しい（アクセスのモジュールには詳しくても、特にエクセルVBAの点）と思うが、大丈夫かな。
本質問は、丸投げ質問状態でプログラムの作成依頼のようであるが、コードは少し長くなり、引き受けるのは難しい。

お礼

いくつかの方法があることがわかり、大変参考になりました。有り難うございました。