実行プロシージャの作成
では、作成したクラスclsTransのメソッドToExcelを使ったプロシージャを作成しましょう。
プロジェクトに標準モジュール「Module1」を追加します。そして、プロシージャレコード全体をExcelに転送するを宣言します。
Sub レコード全体をExcelに転送する() End Sub
まず、DimステートメントにNewキーワードを付けて、クラスからオブジェクトを作成します。
Dim Qex As New clsTrans
次に、プロパティQryNameにクエリ名を、SaveBookNameにブック名をフルパスでセットします。これらは、必ずメソッドToExcelを実行する前にセットしてください。
With Qex .QryName = "商品区分別商品リスト" .SaveBookName = "c:\QryData.xls"
そして、メソッドToExcelを実行します。Excelが起動し、クエリの結果セットがすべてワークシートに転送されます。
.ToExcel
End With
最後に、使用したオブジェクト変数を開放します。
Set Qex = Nothing
レコード数を指定してデータを転送するメソッドの作成
もう1つメソッドを作成しておきましょう。このメソッドは、Excelに転送するレコード数を指定して実行するメソッドです。
オブジェクト変数の宣言
クラスの宣言セクションに、作成する新規ブックへの参照を格納するオブジェクト変数を宣言します。
Dim QryBook As Excel.Workbook
そして、SubプロシージャRecord_Transを作成します。
Public Sub Record_Trans() End Sub
プロシージャの先頭で、Excelオブジェクトへの参照を格納する変数と、ループのカウンタ用変数を1つずつ宣言します。そして、このカウンタ用変数を「1」に初期化しておきます。
Dim RecExcel As Excel.Application Dim i As Integer i = 1
クエリを実行する
DoCmdオブジェクトのRunCommandメソッドを使用して、クエリを実行します。実行するクエリ名は、プロパティQryNameから取得し、読み出し専用で開きます。
DoCmd.OpenQuery QryName, , acReadOnly
そして、CreateObject関数でExcelのインスタンスを作成し、オブジェクト変数に格納して、Excelを表示状態にします。
Set RecExcel = CreateObject("excel.Application") RecExcel.Visible = True
ここまでは、先ほど作成したメソッドToExcelと同じですが、このメソッドではExcelに新しいブックを組み込んであげる必要があります。
先ほど使用した［Office Links］－［Excelに出力］機能は勝手にExcelに新しいブックを追加してくれましたが、ここで行う処理では自分でブックを追加しなくてはなりません。これは、WorkbookオブジェクトのAddメソッドが実行します。
Set QryBook = RecExcel.Workbooks.Add
メソッドを実行し新規ブックを追加すると、メソッドはWorkbookオブジェクトを返してきますので、これをSetステートメントでオブジェクト変数に格納しておきます。なお、この変数は、あとで説明するグラフ作成のプロシージャでも使用しますので、モジュールレベルの変数で宣言しておきます。
クエリ結果をExcelに転送する
このクラスでは、クエリ結果のレコードを1件ずつ選択して、クリップボード経由でExcelのワークシートに転送します。
まず、クエリを実行した時点で、先頭のレコードがカレントレコードになっていますので、これをRunCommandメソッドにacCmdSelectRecordをセットして実行し選択状態にします。次に、RunCommandメソッドにacCmdCopyをセットしてコピーします。これらの操作は、Accessの［編集］メニューにある［レコードの選択］［コピー］と同じ働きをします。
DoCmd.RunCommand acCmdSelectRecord DoCmd.RunCommand acCmdCopy
次に、WorksheetオブジェクトのPasteメソッドを使用して、先頭行のセルに貼り付けます。これで、クエリ結果のレコード1行分の、すべてのフィールドデータがコピーされます。
With QryBook.Worksheets("Sheet1") .Paste Destination:=.Cells(i, 1) End With
そして、ループのカウンタ用変数を1つ増やし、クエリ結果の2行目のコピーを行います。これは、While...Wendステートメントを使用し、16回この「コピー・貼り付け」操作を行います。
ただし、この一行単位のレコードコピーを実行すると、フィールド名が毎回いっしょにコピーされてしまいます。すなわち、クエリ結果では1行コピーしても、ワークシートには2行貼り付けられてしまいます。これでは一覧表が見づらくなってしまいますので、1回コピーしたらフィールド名の行を削除してあげるようにします。
i = i + 1 While i < 16 On Error GoTo FAIL DoCmd.RunCommand acCmdSelectRecord DoCmd.RunCommand acCmdCopy With QryBook.Worksheets("Sheet1") .Paste Destination:=.Cells(i, 1) .Rows(i).Delete End With
レコードを1行コピーしたら、カウンタ用変数の値を1つ増やし、RunCommandメソッドにacCmdRecordsGoToNextをセットして実行します。これで、カレントレコードが次のレコードに移動します。
i = i + 1
DoCmd.RunCommand acCmdRecordsGoToNext
Wend
プロシージャの終了処理
もし、カレントレコードが最後のレコードなのに、次のレコードに移動しようとするとRunCommandメソッドでエラーが発生します。そのため、On Errorステートメントを組み込み、ラベルFAILに処理をジャンプさせます。
ラベルFAILでは、開いたクエリを閉じ、グラフ作成のプロシージャを呼び出して、使用したオブジェクト変数を開放します。
プロシージャ内部で使用する公開しないメソッド（プロシージャ）を呼び出す場合は、Meキーワードをつけてそのプロシージャ名を記述します。
ここでは、グラフ作成の処理を別ルーチンで実行させますので、内部で呼び出して使用するプロシージャCreateGraphを作成しています。
FAIL:
DoCmd.RunCommand acCmdClose
Call Me.CreateGraph
Set QryBook = Nothing
Set RecExcel = Nothing
End Sub
通常、クラスのメソッドは、Publicで宣言されたSubまたはFunctionプロシージャで作成されますが、クラスのメソッドとしてではなく、メソッド内で呼び出して使用するプロシージャが必要なことがあります。そうしないと、多くの処理を行うメソッドではプロシージャのコードがぐちゃぐちゃになってしまうからです。
Friendキーワードを使用して、SubまたはFunctionプロシージャを宣言すると、内部処理用のプロシージャとして扱われ、クラスのメソッドとしては使用できなくなります。今回、グラフ作成の処理と、グラフ作成の元になるセル範囲を取得するプロシージャを、このFriendキーワードで宣言し、内部処理用のプロシージャとしています。Friend Sub CreateGraph() End Sub
