Microsoft Access Club >SampleFile Q & A >インデックス >このページ >次頁 < 前項  サイト内検索

Q&A-026 抽出データごとにExcelシートへ分類出力する一例


タイトル : TransferSpreadsheet acExport時に、Sheetを分ける
記事No : 12116
投稿日 : 2004/03/16(Tue) 17:48
投稿者 : 入門者

OS:NT
Access Version:97

次のようなテーブルがあるとします。
+------+------+------+------+
| 卸  | 客CD | 客名 |納品日|
+------+------+------+------+
| K社 | 0001 | 東京 | 0310 |
+------+------+------+------+
| F社 | 0001 | 東京 | 0311 |
+------+------+------+------+
| F社 | 0002 | 千葉 | 0311 |
+------+------+------+------+
| K社 | 0002 | 千葉 | 0312 |
+------+------+------+------+

DoCmd.TransferSpreadsheet acExportを使って、マクロの実行を1回押すだけで、
卸ごとに別々のSheetにエクスポートすることは可能でしょうか?
(この例で言うと、K社、F社の2Sheetにしたいのですがそのようなことは可能でしょうか。)


> DoCmd.TransferSpreadsheet acExportを使って、マクロの実行を1回押すだけ

DoCmd.TransferSpreadsheet acExport では、実現できません。
Excelオブジェクトライブラリーにある CopyFromRecordset メソッド で可能かと思います。

今回の対応

今回は、

  • Sheetを分類する 卸 の一覧を新規テーブル(tbl_group)に抽出追加します。
  • tbl_sampleを基にしたSQLを作成します。このSQLの卸フィールドに抽出条件設定を行います。
  • 参照設定でDAO、Excelライブラリーを利用可能にします。

方法としては、

  • Do Until 〜 Loop を用いて、tbl_groupテーブルの卸フィールドの値を取得します。
  • 卸フィールドの値を抽出条件として、tbl_sampleテーブルデータから値を抽出します。
  • 抽出した値を CopyFromRecordset メソッド で、新規のブックのSheetに書き込んでいきます。
  • 新規ブックを、Good.xls名でCドライブに保管します。

サンプルテーブルの作成

サンプルテーブルは、下記のように想定します。

ID 客CD 客名 納品日
1 K社 0001 東京 0310
2 F社 0001 東京 0311
3 K社 0002 千葉 0311
4 F社 0002 千葉 0312
  • IDフィールドはオートナンバー型、これ以外はテキスト型です。

参照設定

下記の手順で、参照設定を行います。

  1. VBE(Visual Basic Editor)画面を開き、メニューバーのツールから参照設定ダイアログを開きます。
  2. Microsoft DAO*.* Object Library、Mirosoft Excel*.* Object Library それぞれにチェックを入れて有効にします。

Functionプロシージャの作成

汎用的に利用するため、標準モジュールで作成します。

Function SheetWrite()

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim objXLS As Object
    Dim wstXLS As Excel.Worksheet  'エクセル ワークシート
    Dim rngXLS As Excel.Range 'エクセル 全範囲
    
    Dim mydb As DAO.Database
    Dim myrs As DAO.Recordset
    Dim varData As Variant
    Dim mySQL As Variant
    
    DoCmd.SetWarnings False '警告を無効にします。-----A
    
    'tbl_groupテーブルを作成します。
    DoCmd.RunSQL "SELECT [卸] INTO [tbl_group] FROM [tbl_sample] GROUP BY [卸];"

    Set mydb = CurrentDb()
    Set myrs = mydb.OpenRecordset("tbl_group", dbOpenTable)
        
        '出力先のExcelを利用できるように設定します。
        Set objXLS = Excel.Application
        '新しいブックを作成します。
        objXLS.Workbooks.add '-----B
        
    Do Until myrs.EOF
    
        varData = myrs!卸'-----C
        
        mySQL = "SELECT ID, 卸, 客CD, 客名, 納品日 "
        mySQL = mySQL & "FROM tbl_sample WHERE 卸 = '" & varData & "';" '-----D
            
        Set db = CurrentDb
        Set rs = db.OpenRecordset(mySQL)
        Set wstXLS = Worksheets.add 'ワークシートを追加します。
        wstXLS.Name = varData  'ワークシート名を命名します。

        '以下-----E
        Set rngXLS = wstXLS.Range(Cells(2, 1), _
                                    Cells(rs.RecordCount + 1, rs.Fields.Count))
        rngXLS.CopyFromRecordset rs  'Excelにデータを書き込みます。
                        
        myrs.MoveNext
        
    Loop
        '以下-----F
        objXLS.Visible = True
        ActiveWorkbook.SaveAs ("C:\Good.xls")
        
        DoCmd.SetWarnings True '警告を有効に戻します。
        
    Set rs = Nothing
    Set db = Nothing
    Set objXLS = Nothing
    Set wstXLS = Nothing
    Set rngXLS = Nothing
    Set mydb = Nothing
    Set myrs = Nothing
    
End Function
解説
  1. テーブル作成SQLを実行すると、下記のような警告メッセージが表示されますので、このメッセージを無効にします。RunSQL メソッドを用いてテーブル作成のSQLを実行します。

  1. 新しいブックを作成します。なお、既存のファイルに書き込む場合は、
    objXLS.Workbooks.Open Filename:= "C:\Sample.xls" のようにします。
  2. 変数varDataにtbl_groupテーブルのフィールドデータを代入します。
  3. 変数varDataを抽出条件としてtbl_sampleテーブルを基にしたmySQLを作成します。
  4. Excel側の処理です。データを書き込む範囲を事前に取り込みます。
    CopyFromRecordset メソッドを用いて、レコードセット(mySQL)を書き込みます。
  5. Excel側の処理です。Excelを開き、CドライブにGood.xlsという名前で保存します。

動作確認

  1. FunctionプロシージャSheetWriteをイミディエイトウィンドウで実行します。
  2. 下記のように、Excelが開きます。

  • シート名が、卸フィールドのデータであることを確認します。
留意点
  • DAO3.6でAccess97ファイルを作成していますので、Access97をご利用の方はサンプルファイル使用時に警告メッセージが表示されます。ご注意下さい。

関連ページ

2004/03/18

ページの先頭へ 前ページへ戻る

 


Q&A-026 抽出データごとにExcelシートへ分類出力する一例


 

-Microsoft Access Club-