• ベストアンサー
  • 困ってます

accessからexcelへのエクスポート時のシート

access2002 excel2002 現在accessのクロス集計クエリで各店舗の損益表を作成しフォームからの出力指示でexcelにエクスポートしています。店舗番号を抽出条件にして1店舗ずつ出力しています。 これを店舗番号を指定せずクエリを実行し全店舗分の損益データを店舗ごとのシートに出力するようにしたいのですが、そんなことはできないでしょうか。 店舗,項目 ,8/1 ,8/2 ,8/3 ... 0001,売上 ,10000,12000,13000 0001,原価 , 5000, 5000. 6900 0001,人件費, 4000, 4500, 5000 0002,売上 ,20000,21000,19000 0002,原価 , 9000, 8900. 9000 0002,人件費, 6000, 7000, 6000 上記クエリ結果を シート0001に 店舗,項目 ,8/1 ,8/2 ,8/3 ... 0001,売上 ,10000,12000,13000 0001,原価 , 5000, 5000. 6900 0001,人件費, 4000, 4500, 5000 シート0002に 店舗,項目 ,8/1 ,8/2 ,8/3 ... 0002,売上 ,20000,21000,19000 0002,原価 , 9000, 8900. 9000 0002,人件費, 6000, 7000, 6000 店舗ごとにクエリを作成すればできるような気がするのですが、店舗数が50以上あるのでどうにかひとつのクエリでできないものか考えたのですがどうしても分かりませんでした。 どなたかご存知の方がいらっしゃいましたらご教授願います。

共感・応援の気持ちを伝えよう!

  • 回答数7
  • 閲覧数2129
  • ありがとう数5

質問者が選んだベストアンサー

  • ベストアンサー
  • 回答No.7

Function makeXL() '要参照設定 Microsoft DAO 3.x Object Library Dim qDef As DAO.QueryDef Dim strSQLa As String, strSQLb As String Dim rs As DAO.Recordset |strSQLa = "PARAMETERS [Forms]![F日付入力]![日付] DateTime;" _ |    & " TRANSFORM Sum(Q損益.Sales07) AS 売上の合計" _ |    & " SELECT Q損益.店舗, 項目マスタ.項目" _ |    & " FROM Q損益 INNER JOIN 項目マスタ ON Q損益.科目No = 項目マスタ.[No]" | |'WHERE (((Q損益.店舗)="0000000000001")) | |strSQLb = " GROUP BY Q損益.店舗, Q損益.科目No, 項目マスタ.項目" _ |    & " ORDER BY Q損益.科目No" _ |    & " PIVOT Format$([SalesDate],""yyyy/mm/dd(aaa)"");" L__ここまででご質問者の作成したクエリからWhere句を除く部分をSQL文として変数に格納 Set rs = CurrentDb.OpenRecordset _     ("select 店舗番号 from 店舗マスタ order by 店舗番号", dbOpenSnapshot) __レコードセットの取得      For Each qDef In CurrentDb.QueryDefs   If qDef.Name = "Q_TMP" Then     DoCmd.DeleteObject acQuery, qDef.Name   End If Next qDef __クエリの中に、「Q_TMP」が有れば削除 Set qDef = CurrentDb.CreateQueryDef("Q_TMP") __クエリ「Q_TMP」の作成   一個上でクエリの削除を行っていますが   本来なら無ければ作成の方が良さそうですが・・手抜きの雰囲気 Do Until rs.EOF __レコードセットが、お終いまで繰り返す   qDef.SQL = strSQLa _   & " where Q損益.店舗 = '" & rs!店舗番号 & "'" _   & strSQLb   __上で変数に格納したSQL文にWhere句を追加し、クエリ「Q_TMP」     のSQL文としています        Debug.Print qDef.SQL   __↑この辺でF9を押してブレークポイントを作成しAccessのウィンドウに     戻ってクエリ「Q_TMP」が書き換わっているのを見てね     また F8 で一行ずつ実行されますので変数などが変わって行くのも確認        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Q_TMP", _     CurrentProject.Path & "\" & Format(Date, "yyyymm") & "outXL.xls", _     True, rs!店舗番号        '↑cstr(rs!店舗番号) かも?   rs.MoveNext__次のレコードセットに移動 Loop__繰り返し |rs.Close: Set rs = Nothing |Set qDef = Nothing |__このモジュールを抜けるときに白紙状態になるはずなのですが    オマジナイです MsgBox "お待たせ" End Function それぞれの関数やプロパティはカーソルを合わせて、F1 で該当するヘルプが出ますので 調べてください 私からはこれまでです。 自分で調べて覚えれば確実に自身の血となり肉(脳みそ)となります がんばって下さい。

共感・感謝の気持ちを伝えよう!

質問者からのお礼

ありがとうございました。 自分でもいろいろ調べてみました。nicotinismさんの解説と合わせて少し理解できてきました。 少し手を加え、抽出条件を追加してみました。最初はうまくいかなかったのですが、いろいろ試しているうちにうまくいきました。 このたびは、ありがとうございました。

その他の回答 (6)

  • 回答No.6

多分、「店舗マスタ」のようなテーブルが有るでしょうから・・ 「店舗マスタ」 店舗番号 テキスト型 主キーとして、レコードセットを取得して。 Function makeXL() '要参照設定 Microsoft DAO 3.x Object Library Dim qDef As DAO.QueryDef Dim strSQLa As String, strSQLb As String Dim rs As DAO.Recordset strSQLa = "PARAMETERS [Forms]![F日付入力]![日付] DateTime;" _     & " TRANSFORM Sum(Q損益.Sales07) AS 売上の合計" _     & " SELECT Q損益.店舗, 項目マスタ.項目" _     & " FROM Q損益 INNER JOIN 項目マスタ ON Q損益.科目No = 項目マスタ.[No]" 'WHERE (((Q損益.店舗)="0000000000001")) strSQLb = " GROUP BY Q損益.店舗, Q損益.科目No, 項目マスタ.項目" _     & " ORDER BY Q損益.科目No" _     & " PIVOT Format$([SalesDate],""yyyy/mm/dd(aaa)"");" Set rs = CurrentDb.OpenRecordset _     ("select 店舗番号 from 店舗マスタ order by 店舗番号", dbOpenSnapshot) For Each qDef In CurrentDb.QueryDefs   If qDef.Name = "Q_TMP" Then     DoCmd.DeleteObject acQuery, qDef.Name   End If Next qDef Set qDef = CurrentDb.CreateQueryDef("Q_TMP") Do Until rs.EOF   qDef.SQL = strSQLa _   & " where Q損益.店舗 = '" & rs!店舗番号 & "'" _   & strSQLb   Debug.Print qDef.SQL   DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Q_TMP", _     CurrentProject.Path & "\" & Format(Date, "yyyymm") & "outXL.xls", _     True, rs!店舗番号        '↑cstr(rs!店舗番号) かも?   rs.MoveNext Loop rs.Close: Set rs = Nothing Set qDef = Nothing MsgBox "お待たせ" End Function

共感・感謝の気持ちを伝えよう!

質問者からのお礼

出来ました!! これはまさにプロの技だと思います。 ありがとうございます。本当に感謝いたします。 このプロシージャは非常にすばらしいので今後もいろいろと使用できると思います。 しかし悲しいかな、ここまでくると私にはどうにも理解できません。 そこでお願いなのですが、もし可能であるならば、このプロシージャについて簡単で結構ですので、少し解説していただけませんでしょうか。 厚かましいお願いかもしれませんが、よろしくお願いします。

  • 回答No.5

これで如何でしょう? Function makeXL() '要参照設定 Microsoft DAO 3.x Object Library Dim qDef As DAO.QueryDef Dim strSQLa As String, strSQLb As String Dim numSheet As Integer strSQLa = "PARAMETERS [Forms]![F日付入力]![日付] DateTime;" _     & " TRANSFORM Sum(Q損益.Sales07) AS 売上の合計" _     & " SELECT Q損益.店舗, 項目マスタ.項目" _     & " FROM Q損益 INNER JOIN 項目マスタ ON Q損益.科目No = 項目マスタ.[No]" 'WHERE (((Q損益.店舗)="0000000000001")) strSQLb = " GROUP BY Q損益.店舗, Q損益.科目No, 項目マスタ.項目" _     & " ORDER BY Q損益.科目No" _     & " PIVOT Format$([SalesDate],""yyyy/mm/dd(aaa)"");" For Each qDef In CurrentDb.QueryDefs   If qDef.Name = "Q_TMP" Then     DoCmd.DeleteObject acQuery, qDef.Name   End If Next qDef Set qDef = CurrentDb.CreateQueryDef("Q_TMP") For numSheet = 1 To 50   qDef.SQL = strSQLa _   & " where Q損益.店舗 = '" & Format(numSheet, "0000000000000") & "'" _   & strSQLb   Debug.Print qDef.SQL      DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Q_TMP", _     CurrentProject.Path & "\" & Format(Date, "yyyymm") & "outXL.xls", _     True, Format(numSheet, "0000000000000") Next numSheet Set qDef = Nothing MsgBox "お待たせ" End Function

共感・感謝の気持ちを伝えよう!

質問者からのお礼

で、で、できました!! 深夜までお付き合いいただき、ありがとうございました。 これができれば、もうOKとしたいところなのですが、今実行してみて気が付いたことがあります。 店舗番号は、1から50の連番ではないのです。番号が飛び飛びになっています。この場合はどのように対処したらいいのでしょうか。 暇なときで結構です。よろしくお願いします。

  • 回答No.4

クロス集計クエリは出来ていますよね? でしたらそのクエリで、店舗が0001の抽出条件を設定したクエリを作成し そのSQLビューを提示してみてください。 また 止まったときに、Ctrl + G を押してイミディエイトウィンドウを出して そこに記述されているのも提示してください。 以上2点お願いします。 追伸、補足欄ですと当方で気が付かない場合がありますので お礼欄に記述がありますと当方にメールが入りますので助かります。

共感・感謝の気持ちを伝えよう!

質問者からのお礼

たびたびすみません。 こちらがsqlです。 PARAMETERS [Forms]![F日付入力]![日付] DateTime; TRANSFORM Sum(Q損益.Sales07) AS 売上の合計 SELECT Q損益.店舗, 項目マスタ.項目 FROM Q損益 INNER JOIN 項目マスタ ON Q損益.科目No = 項目マスタ.[No] WHERE (((Q損益.店舗)="0000000000001")) GROUP BY Q損益.店舗, Q損益.科目No, 項目マスタ.項目 ORDER BY Q損益.科目No PIVOT Format$([SalesDate],"yyyy/mm/dd(aaa)"); こちらがイミディエイトウィンドウです。 PARAMETERS [Forms]![F日付入力]![日付] DateTime TRANSFORM Sum(Q損益.Sales07) AS 売上の合計 SELECT Q損益.店舗, 項目マスタ.項目 FROM Q損益 INNER JOIN 項目マスタ ON Q損益.科目No=項目マスタ.[No] WHERE (((Q損益.店舗)="0000000000001")) GROUP BY Q損益.店舗, Q損益.科目No, 項目マスタ.項目 ORDER BY Q損益.科目No PIVOT Format$([SalesDate],"yyyy/mm/dd(aaa)") 両者の違いは私には分かりませんでした。 ちなみに、クエリ『Q1』は単体では問題なく動きます。 よろしくお願いします。

  • 回答No.3

いけね where 番号 = ↓ where 店舗 = ですね 後は大丈夫かな? ただ今、酔って候 注、前回レスの時は「しらふ」なのに (^_^;) 勝手な言い草ですけど・・ 回答の内容をヒントに調べてね

共感・感謝の気持ちを伝えよう!

質問者からの補足

回答ありがとうございます。 『番号』を『店舗』に変更しても構文エラーになってしまいます。他にもいろいろ試してみましたが、構文エラーはかわりませんでした。 何がいけなのでしょうか。

  • 回答No.2

お節介なMicrosoftでも単一のクエリでExcelのシートごとに分けて出力は出来ません。 VBA になりますが クロス集計クエリの名前がQ1 、Q1 に抽出条件は指定していない、 店舗はテキスト型だとして Where 句 を順次替えてやれば良いのでは? 下記を標準モジュールにコピペ それをマクロからアクション → プロシージャの実行 『 プロシージャ名 makeXL() 』 とか、フォームにコマンドボタンを置いて呼び出すとか? Function makeXL() '要参照設定 Microsoft DAO 3.x Object Library Dim qDef As DAO.QueryDef Dim strSql As String Dim numSheet As Integer Set qDef = CurrentDb.QueryDefs("Q1") strSql = Replace(qDef.SQL, ";", "") Debug.Print strSql Set qDef = Nothing For Each qDef In CurrentDb.QueryDefs   If qDef.Name = "Q_TMP" Then     DoCmd.DeleteObject acQuery, qDef.Name   End If Next qDef Set qDef = CurrentDb.CreateQueryDef("Q_TMP") For numSheet = 1 To 50   qDef.SQL = strSql & " where 番号 = '" & 店舗 & "'"   DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Q_TMP", _     CurrentProject.Path & "\" & Format(Date, "yyyymm") & "outXL.xls", _     True, Format(numSheet, "0000") Next numSheet Set qDef = Nothing msgbox "お待たせ" End Function ※参照設定は、Alt + F11 でVBAのウィンドウが開きますので ツール → 参照設定 で探して、 Microsoft DAO 3.6 Object Libraryにチェックを入れてください。

共感・感謝の気持ちを伝えよう!

質問者からの補足

丁寧な回答ありがとうございます。 早速マクロを作成しプロシージャを実行してみました。 すると   qDef.SQL = strSql & " where 番号 = '" & 店舗 & "'" この部分が、『PARAMETER句の構文エラー』 となってしまいました。何がいけないのでしょうか。 よろしくお願いします。

  • 回答No.1

フォームからの出力指示でExcelへエクスポートしているのであれば、フィルタかWhere条件を設定して、クロス集計クエリをOPENすればいいのでは??

共感・感謝の気持ちを伝えよう!

質問者からの補足

>フィルタかWhere条件を設定して、クロス集計クエリ >をOPENすればいいのでは?? すみません。そう言われてもやり方がわかりません。具体的にどのようにしたらいいのでしょうか。 よろしくお願いします。

関連するQ&A

  • ACCESS2002のエクスポートで・・・

    特に困ってはいないのですが不思議に思ったので質問します。 OS:WinXP Pro 環境:ACCESS2002およびEXCEL2002 1.ACCESSでクエリ「TEST1」をEXCELにエクスポートします。 2.エクスポートして作成されたEXCELファイルを開き、シート名「TEST1」を「TEST2」に変更します。 3.再度、ACCESSでクエリ「TEST1」を1.で作成されたEXCELファイルを指定してエクスポートすると「オブジェクト「TEST1」は存在します。置き換えますか?」旨のメッセージが表示されます。 指定したEXCELファイルにはシート「TEST1」は存在しないのになぜそのようなメッセージが表示されるのでしょうか?ちなみそのまま出力するとクエリ「TEST1」のデータはEXCELのシート「TEST2」に出力されます。

  • ACCESS2010Excelエクスポート1行目に

    おしえてください。 ACCESS2010、「クエリ」作成データを Excelへエクスポートを行う際、 ヘッター項目の「上の行」に固定の文字を入れ、Excelへエクスポートさせたいのですが、 どのようにすればよいでしょうか。 自分なりに調べたのですが答えが見つからずお力を貸してください。

  • ACCESSのクエリーからEXCELにエクスポート時のエラー

    WINDOWS ME上でACCESS 97を使っています。 ACCESSのクエリーをEXCELにエクスポートしています。 10個のクエリーを1つのファイルにエクスポートしているのでクエリー名のシートが10個ファイルにできます。そのEXCELのファイルを更新して毎月作成していくのですが、そのうちのいくつかのシートが上書きできません。まず「同じ名前がありますが名前を変更しますか?」という旨の確認がありそのはまま「はい」にするとすんなり上書きしてくれるシートと「同じ名前のシートが存在します」とエラー出て、データーを上書きできないシートが同じファイル内にあります。シートを削除してエクスポートすればエラーはかからないのですが、集計表シートと計算式をいれているのでその都度削除して計算式を再度作成していくのも大変です。なにか思い当たることがありますか?

  • Excelにデータをエクスポートする

    Access2003のクエリをExcel2003へデータエクスポートするのに、一行目の項目名を同じものにしておくと、既存の名前を付けたExcelブックの既存の名前を付けたワークシートにデータを書き込むことができていました。日々増えて行くAccessデータを抽出してExcelで利用するのに使っていました。 同じことをAccess2010からExcel2010へのエクスポートで実行すると、Excelブックが上書きされて、Excelブック内の他の既存ワークシートがなくなり、エクスポートされたデータのワークシートのみになってしまいます。 2003のときはエクスポートしてそのまま使えたのに、2010では手間が増えてしまいました。2010でも同じように、既存のワークシートに書き込むことをできないものなのでしょうか?

  • ACCESSのレポートをEXCELにエクスポート

    ACCESS、EXCEL、ともに2000です。 VBAでACCESSで作成したレポートを EXCEL(シート指定)してエクスポートすることは出来ませんか? 出来ましたら、書式もそのままでエクスポートしたいのですが・・・。 ご面倒かとは思いますが、お答え頂ければ幸いです。

  • Access2010 シート追加をしたい。

    クエリをエクセルにエクスポートしたのですが、例えば、日本一をいうワークシートに1の項目2の項目と追加シートをしていきたいのですが、どうしても上書きしますか?と保存の際に聞かれ、追加シートができません。どのようにすればできるのでしょうか? Access2010を使用しています。 宜しくお願いします。

  • accessからExcelへのエクスポート

    今、access初心者の私ですが、仕事上必要なので、クエリをエクセルにエクスポートしています。 しかし、実際に必要なデータは下記の例のように、クエリの演算したフィールドも含めて一部であって、クエリで表示させたフィールド全部がいるわけではないのです。 例)  <クエリ>               <エクセル> A B C D E F   ―エクスポート→  A C E F 演算フィールドの関係でどうしても表示させておかないと演算結果がエラーになってしまうので、クエリの表の状態では「A~F」はどうしてもいるのです。 これを、今はエクセルにエクスポートしてから「B」と「D」のフィールドを削除しているのですが、フィールドを選んでエクスポートってできないでしょうか? どなたかいい方法があれば教えて下さい。お願いします。

  • AccessのマクロでExcelにエクスポートしたい

    レコード数が5万程度あるクエリを マクロでExcelにエクスポートしたいのですが、 「出力」を選ぶと、「出力する行が多すぎて、指定した出力形式またはMicrosoft Accessの制限を越えています。」のエラーメッセージが表示されてしまいます。 マクロではなく、普通にエクスポートする際は、 Excelのバージョンを選択できるので、正常処理できるのですが、マクロの場合はどうすればいいのでしょうか? 宜しくお願いします。

  • AccessのクエリをExcelへエクスポートする際

    Access2002でクエリをExcel2002へエクスポートしたのですが、エラーではないのですが、気になることがあります。 なぜか、データの並びが違うのです。なぜクエリの時とExcelシートにした時で勝手にデータが並べ変わっているのか気になって。 一応、過去の質問を調べましたが似た質問を見つけることができなかったので、自分で質問してみます。 ご存知の方がいたら、教えてください。 もし、過去に出ているなら申し訳ないですが、どの質問か教えてください。よろしくお願いします。

  • Access上で作成したレポートのExcelへのエクスポート

    今日は。 Accessのクエリーを元に作成したレポートをExcelにエクスポートする際、列の並び順が変わってしまいます。 Excelにエクスポートする際、並び順をレポート上で表示されているのと同じ順番で書き出すにはどうしたらよいのでしょうか? 宜しくお願い致します。