Excelへのデータ転送メソッドの作成
最初に、クエリ結果の全レコードを一気にExcelに転送するプロシージャToExcelを作成します。これは、引数を持たないSubプロシージャで宣言します。
Public Sub ToExcel() End Sub
Excelのインスタンスを作成する
まず、冒頭に、Excelオブジェクトを格納する変数を宣言します。
Dim ex As Excel.Application
そして、CreateObject関数でExcelのインスタンスを作成します。引数のプログラムIDは「excel.Application」になります。
関数が無事Excelのインスタンスを作成すると、オブジェクトへの参照を返してきますので、Setステートメントでオブジェクト変数exに格納します。これで、変数exを使ってExcelのすべてのオブジェクトをAccessから操作できるようになります。
Set ex = CreateObject("excel.Application")
CreateObject関数はExcelのインスタンスを作成するだけなので、ApplicationオブジェクトのVisibleプロパティにTrueをセットして、Excelを表示させます。
ex.Visible = True
クエリを実行するコードの作成
次に、プロパティQryName設定されているクエリを実行します。この操作は、DoCmdオブジェクトのOpenQueryメソッドを使用します。
DoCmdオブジェクトは、Accessのアクションを担当するオブジェクトで、アクションの実行はこのオブジェクトのメソッドで提供されています。
OpenQueryメソッドは、そのアクションの1つ「クエリを開く」操作を実行するメソッドです。3つの引数を持ち、第1引数に実行するクエリ名、第2引数にクエリの実行形態を、第3引数に編集モードを指定します。ここでは、開くクエリ名にプロパティQryNameをあてて、実行時にクエリ名をセットするようにしています。また、このクラスはデータの取得を目的にしていますので、編集モードはacReadOnlyをセットし読み出し専用で開くようにします。
DoCmd.OpenQuery QryName, , acReadOnly
このメソッドを実行すると、クエリを開き、選択クエリの場合にはクエリ結果を表示します。
クエリ結果をExcelに転送する
クエリ結果をExcelに転送するには、DoCmdオブジェクトのRunCommandメソッドを使用します。このメソッドは、Accessに組み込まれているメニュー、またはツールバーのコマンドを実行します。引数に実行したいコマンド名を定数でセットします。
Accessの［ツール］メニューには、［Office Links］-［Excelに出力］というコマンドがあり、これを使うとクエリ結果をそのまますべてExcelに転送できます。
このコマンドを担当するのが、定数acCmdOutputToExcelです。この値をRunCommandメソッドの引数にセットしメソッドを実行すると、Excelに新しいブックを追加しクエリの結果セットをコピーします。
DoCmd.RunCommand acCmdOutputToExcel
Excelへの転送が終われば、クエリを閉じます。これは、RunCommandメソッドの引数に定数acCmdCloseをセットして実行します。これは、Accessメニューの［ファイル］-［閉じる］と同じ働きを実行します。
DoCmd.RunCommand acCmdClose
これで、Excelへのデータ転送を実行するクラスのメソッドToExcelのできあがりです。
ブックを保存する
次に、ブックを保存する処理を作成します。RunCommandメソッドでExcelへの転送を行った場合は、作成されるブックはクエリと同じ名前のブック名が付けられます。このブックを保存するパス名も含め、独自のファイル名で保存したいので、専用のメソッドを用意しておきます。
この専用メソッドは、Excelのアクティブなブックを対象に、WorkbookオブジェクトのSaveAsメソッドを実行します。引数に保存するブック名をフルパスで指定しますが、ここではクラスのプロパティSaveBookNameから値をオブジェクト取り出します。
SaveBookNameプロパティに何も設定されていない場合に備えて、エラー処理を組み込んでおきます。
If SaveBookName <> "" Then ex.ActiveWorkbook.SaveAs SaveBookName Else MsgBox "保存するブック名をSaveBookNameプロパティで設定してください" End If
最後に、使用したオブジェクト変数をメモリから開放します。
Set ex = Nothing
