Accessの条件つき抽出＞テーブル作成。

質問

「T_マスタ」
・氏名ID
・氏名
・会社グループ

「T_サブ」
・氏名ID
・データ年

というようなテーブルがあり、この2ツから「Q_検索」というクエリを作成しています。

このクエリを元に検索フォームを作成しました。

この検索フォームで「データ年」を選んで「エクスポート」を押すと、
会社グループが「110」のものだけを抽出して「T_Excel_110」という
テーブルが作成されるようにしたいのです。

「エクスポート」ボタンのコードは

----------------------------------------
（検索用のコード）省略

DoCmd.RunSQL "SELECT [T_マスタ].[氏名ID], [T_マスタ].[氏名], [T_マスタ].[会社グループ],[T_サブ].[データ年], INTO T_Excel_110" _
& " FROM (T_マスタ INNER JOIN T_サブ ON [T_マスタ].[氏名ID]=[T_サブ].[氏名ID]) " _
& " WHERE ((([T_マスタ].[会社グループID])="110"))" & WhereCond

stDocName = "T_Excel_110"
DoCmd.OpenTable stDocName, acNormal, acEdit

MsgBox "[ファイル]-[エクスポート]でExcelファイルを指定してください。"

としたのですが、エラーになってしまいます。
いったいどこを直せばいいのでしょうか？

回答No.4

yoisho

前回の回答に忘れていたことがありましたので、追加です。

Excelファイルの保存先とファイル名をユーザーに指定させたければ、データを一時ファイルとしてエクスポートした後これを Excelで開いて、名前を付けて保存してもらうのはいかがでしょうか？

例えば、以下のコードを末尾に加えてみてください。

Dim objApplication As Object
'以下の２行は前のコードで記述済みですが、念のため書いておきます。
'（Dim strOutputFile As String）
'（strOutputFile = "C:\Test.xls"）
MsgBox "開いたExcelワークブックを" & vbCr & vbCr & "名前を付けて保存してください。"
Set objApplication = CreateObject("Excel.Application")
objApplication.Workbooks.Open strOutputFile
objApplication.Visible = True

回答No.3

yoisho

回答が遅くなりましてスミマセン。
（このところちょっと忙しくて、時間がとれなかったものですから。）

エラーメッセージを見ると、
クエリ式'((([T_マスタ].[会社グループ])='110'))(Q_検索データ年 LIKE ’*2001*')'・・・～
の　「 '110')) 【この部分】 (Q_検索データ年 」　の「AND」が抜けていますね。
WhereCond = Mid(WhereCond, strCount + 1)
で、意識的に削除なさっているようですが、これが原因かと思います。

また、「Q_検索」クエリーのフィールドを使うなら、SQLステートメントの FROM の後にこのクエリーを JOIN で結合して入れておく必要があります。
（というか、この場合なら[会社グループ]や[データ年]フィールドは[T_マスタ]や[T_サブ]に存在しますので、不要のだと思いますが。）

それから、VBAでエクセルへエクスポートの方法ですが、
出力する Excellファイル名を（フルパスで）指定できるなら、TransferSpreadsheetメソッド（ＨＥＬＰで確認してください）を利用すれば可能だと思います。

本来なら、Excellファイルをリンクテーブルにして DAO か ADO で書き込んでやるのが本道で、パフォーマンス（処理速度）も良いかと思いますが、コードが面倒なのでとりあえずテーブル（またはクエリー）を直接エクスポートする方法を考えてみました。

１．テーブル作成クエリーでテーブルをつくり、これを出力する方法
（今回なさっている方法を VBA で連続処理するやり方です。）

Dim I As Integer, str会社グループ As String, strOutputFile As String

strOutputFile = "C:\Test.xls" '出力する Excell のブック名（フルパスで記述）

For I = 110 To 230 Step 10

str会社グループ = CStr(I)

'一時的に書き込みに利用するテーブルを作成
DoCmd.RunSQL "SELECT [T_マスタ].[氏名ID], [T_マスタ].[氏名],[T_マスタ].[会社グループ], [T_サブ].[データ年]" _
& " INTO T_Excel_" & str会社グループ _
& " FROM T_マスタ INNER JOIN T_サブ ON [T_マスタ].[氏名ID]=[T_サブ].[氏名ID]" _
& " WHERE 会社グループ='" & str会社グループ & "' AND データ年 like '*" & Me!データ年 & "*'"

'テーブルを Exellシートに出力
DoCmd.TransferSpreadsheet transfertype:=acExport, spreadsheettype:=acSpreadsheetTypeExcel97, _
tablename:="T_Excel_" & str会社グループ, filename:=strOutputFile

'一時テーブルを削除
DoCmd.DeleteObject acTable, "T_Excel_" & str会社グループ

Next I

２．（テーブルではなく）選択クエリーをつくり、この抽出条件を変えて出力する方法
（クエリーを一つ作るだけでテーブルをいくつも作らないので、多少パフォーマンスは良いと思いますが、クエリーの抽出条件の記述がちょっと裏技っぽくなってしまいます。）

まず標準モジュール（フォームのクラスモジュールではだめです）に、Public変数「str会社グループ」と Public Function「func会社グループ」を記述します。
（クエリーの抽出条件に値を渡す際に、変数を直接記述できないために、ユーザー関数を定義します。）

Public str会社グループ As String

Public Function func会社グループ()
func会社グループ = str会社グループ
End Function

以下が、フォームモジュールの記述例です。

Dim dbs As DAO.Database, qdf As DAO.QueryDef
Dim I As Integer, strSQL As String, strOutputFile As String, str会社グループ As String

Set dbs = CurrentDb

strOutputFile = "C:\Test.xls" '出力する Excell のブック名（フルパスで記述）

'一時的に書き込みに利用する選択クエリーを作成
strSQL = "SELECT [T_マスタ].[氏名ID], [T_マスタ].[氏名],[T_マスタ].[会社グループ], [T_サブ].[データ年]" _
& " FROM T_マスタ INNER JOIN T_サブ ON [T_マスタ].[氏名ID]=[T_サブ].[氏名ID]" _
& " WHERE 会社グループ=func会社グループ() AND データ年 like '*' & [Forms]![フォーム7]![データ年] & '*'"
Set qdf = dbs.CreateQueryDef("Q_データ出力", strSQL)

For I = 110 To 230 Step 10

str会社グループ = CStr(I)

'クエリーを Exellシートに出力
'range引数で、Exellブックのワークシート名を指定しています。
DoCmd.TransferSpreadsheet transfertype:=acExport, spreadsheettype:=acSpreadsheetTypeExcel97, _
tablename:="Q_データ出力", filename:=strOutputFile, range:="会社Ｇ：" & str会社グループ

Next I

DoCmd.DeleteObject acQuery, "Q_データ出力"
Set dbs = Nothing

AccessのＨＥＬＰでは、エクスポートの場合 range 引数を指定しないとなっていますが、実際は使えます。（問題あるのかな？）

さて、Excellファイル名を（フルパスで）指定できない（ファイルを探して、フォルダーとファイル名前を得る必要がある）場合ですが、
Office Developer を使っていらっしゃるのでしたら、コモンダイアログの ActiveX を利用すれば、比較的容易にファイルを探すダイアログが作れますが、Office Pro の場合はコモンダイアログは使えませんから、結構面倒ですね。
（Windows API を利用すれば、できないことはないようですが・・・http://www.okweb.ne.jp/kotaeru.php3?q=275155）

コードのテストをしていません（ご容赦ください）ので、自信なしのアドバイスです。

回答No.2

yoisho

コードを見て気付いたところをお知らせします。
テストはしていませんのでこれだけの問題なのかはわかりませんが、とりあえず確認してみてください。

～ [T_サブ].[データ年], INTO ～　の INTO の前の「,」は不要。

～ WHERE ((([T_マスタ].[会社グループID])="110")) ～　の [会社グループID] は、[会社グループ] の間違い？

同じ個所で、"110" は、[会社グループ]フィールドが数値型なら、「"」 は不要。
テキスト型なら 「'110'（または ""110""）」 （HELP で「文字列内でのクォーテーション」を確認してください。）

SQLステートメントを書く際は、クエリーをデザインビューで作ってから、その SQLビューを修正してやる方が、簡単で間違いも少ないと思います。

おまけですが、
MsgBox "[ファイル]-[エクスポート]でExcelファイルを指定してください。" 　の後ろに、
DoCmd.RunCommand acCmdSaveAs
と記述してやると、[ファイル]-[エクスポート]　の操作まで VBA で処理できます。

お礼

回答ありがとうございます。

別の仕事にとりかかってしまって、実験＆お返事遅れてしまいました。

実際の状態をもう少し詳しく書きますね。
「T_マスタ」
・氏名ID
・氏名
・会社グループ

「T_サブ」
・氏名ID
・データ年

「エクスポート」ボタンのコードは

----------------------------------------
Dim SQL As String
Dim WhereCond As String
Dim condKaisyagroup As String
Dim condNendo As String
Dim tempOper As String
Dim strCount As Long
Dim tempCond As String

WhereCond = "" '変数の初期化。本当はいらないけど明示的に。

Select Case Me!fraOper.Value
Case 1 'and
tempOper = " AND "
strCount = 5
Case 2 'or
tempOper = " OR "
strCount = 4
End Select

'会社グループ
If Me!会社グループ.Value <> "" Then
condKaisyagroup = "(Q_検索.会社グループ like '*" & Me!会社グループ.Value & "*')"
WhereCond = WhereCond & tempOper & condKaisyagroup
End If

'データ年
If Me!データ年.Value <> "" Then
condNendo = "(Q_検索.データ年 like '*" & Me!データ年.Value & "*')"
WhereCond = WhereCond & " AND " & condNendo
End If

WhereCond = Mid(WhereCond, strCount + 1)

DoCmd.RunSQL "SELECT [T_マスタ].[氏名ID], [T_マスタ].[氏名],[T_マスタ].[会社グループID], [T_サブ].[データ年]] INTO T_Excel_110" _
& " FROM (T_マスタ INNER JOIN T_サブ ON [T_マスタ].[氏名ID]=[T_サブ].[氏名ID])" _
& " WHERE ((([T_マスタ].[会社グループ])='110'))" & WhereCond

DoCmd.RunSQL "SELECT [T_マスタ].[氏名ID], [T_マスタ].[氏名],[T_マスタ].[会社グループID], [T_サブ].[データ年]] INTO T_Excel_110" _
& " FROM (T_マスタ INNER JOIN T_サブ ON [T_マスタ].[氏名ID]=[T_サブ].[氏名ID])" _
& " WHERE ((([T_マスタ].[会社グループ])='120'))" & WhereCond

:
:
:
(会社グループが「230」まで）

stDocName = "T_Excel_110"
DoCmd.OpenTable stDocName, acNormal, acEdit

MsgBox "[ファイル]-[エクスポート]でExcelファイルを指定してください。"

End Sub

----------------------------------------
というようにしました。
これで「110」・「120」・・・「230」と会社グループごとのテーブルを作成することができたのですが、
検索（2001年度とか2002年度と指定した場合）が
----------------------------------------
実行時エラー'3075';

クエリ式'((([T_マスタ].[会社グループ])='110'))(Q_検索データ年 LIKE ’*2001*')'の'.'、'!'、または'()'の使い方が正しくありません。
----------------------------------------

と出てしまうのです。
やはり検索させるためのところがどこか違うのだとは思うのですが、わかりません。

それと、1つ1つテーブルを作成していくのが結構量が多いので、できれば、検索した結果を、
グループごとにエクセルへエクスポート、なんてことはできないでしょうか？

申し訳ありませんが、よろしくお願いします。

回答No.1

bin-chan

どこで、どういうエラーになりますか？

作成なさろうとしている「T_Excel_110」はACCESS上のテーブルでしょうか？
いったん"Insert into T_Excel_110 select * from Q_検索"で
テーブル化した方がわかりやすいのでは？

お礼

回答ありがとうございます。

別の仕事にとりかかってしまい、実験＆お返事が遅れてしまいました。

エラーは
----------------------------------------
実行時エラー'3075';

クエリ式'((([T_マスタ].[会社グループ])='110'))(Q_検索データ年 LIKE ’*2001*')'の'.'、'!'、または'()'の使い方が正しくありません。
----------------------------------------
だったと思います。

＞作成なさろうとしている「T_Excel_110」はACCESS上のテーブルでしょうか？
はい、そうです。

＞いったん"Insert into T_Excel_110 select * from Q_検索"で
テーブル化した方がわかりやすいのでは？

これは、検索結果を一度テーブルにして、それをまた抽出してテーブルを作成する、ということでしょうか？