Access VBAでExcelシートを操作するTips

アクセスのクエリーで抽出したデータを、エクセルシートに貼り付けることって結構ある。でも、データの種類が沢山あると、作業が怖ろしく大変。

しかも、悪いことに、この手の作業って簡単だから、誰でも出来ちゃう。その結果、一生懸命やったところで、出来て当たり前って顔されて、アホみたい。

そこで、クエリーからのデータの抽出を、ＳＱＬをループで生成して、そのつど、アクセスのモジュールで生成したエクセルのインスタンスを通じて、 エクセルシートにクエリーの結果を出力できれば、超簡単。しかも、正確。その上、早い。クエリーの結果を、エクセルシートに出力するのが、テクニックのポ イント。マクロで、クエリーの結果をエクセルシートに出せるけど、あれだと、クエリーの条件を連続的に変えられないし、シート名も指定できないから、不 便。

（事前準備）　
事前バインディングを設定する
（モジュール→ツール→参照設定→Microsoft Excel 11.0 Object Libraryにチェック)

(注意)
実行時バインディングだと、コードの補完機能が効かないので、不便。事前バインディングの方がよい。


---------------------------------------------------

Public Sub OutPut_to_xls()

'エクセルへのインスタンス
Dim xlapp As Excel.Application
Dim mysht As Worksheet

'制御変数
Dim i, j As Integer

'レコードセット関係
Dim rst As ADODB.Recordset
Dim cn As ADODB.Connection


'========================================
'プログラムの開始
'========================================


'各種インスタンスを確立
Set rst = New ADODB.Recordset
Set cn = CurrentProject.Connection
Set xlapp = New Excel.Application


'========================================
'エクセルの操作
'========================================


'フルパスを指定してエクセルを開く。その後、新シートを追加
With xlapp
.Workbooks.Open ("J:\My_files\CreateObject\test.xls")
.Worksheets.Add
End With

'（注！！）Activeworkbook.Activesheetをつかうと、エクセルのインスタンスを閉じることが出来ないから、つかっちゃ だめ。コレがわからなくて、エクセルインスタンスが閉じず苦労した！！これって、MSのバグだよね。つめり、Activesheetを
'Set mysht = ActiveWorkbook.ActiveSheet
'って設定するとダメデスYo!


'インデックスを指定して、シートオブヘクトへ代入しよっと
Set mysht = xlapp.Workbooks(1).Worksheets(1)
'シート名を指定（重複するとエラーだよ）
mysht.name = "test5"

'レコードセットを開く
rst.Open "crs1", cn, adOpenKeyset, adLockOptimistic


'表頭をコピー(Cellプロパティーを使うと、エラーを出す噂も・・・・）
For i = 0 To rst.Fields.Count - 1
mysht.Cells(1, i + 1).Value = rst.Fields(i).name
Next i

'レコードセットの内容をコピー
mysht.Range("A2").CopyFromRecordset rst

'CopyFromRecordsetを使わない方法
'まずクエリーの最終列まで、Fieldプロパティーのインデックスを使ってアクセス。その後、次の行へカーソルを動かす。これを、最終行まで繰り返す。EOFがTrueになったら、ループ脱出。
'j = 2
'rst.MoveFirst
'Do Until rst.EOF
' For i = 0 To rst.Fields.Count - 1
' mysht.Cells(j, i + 1) = rst.Fields(i)
' Next i
' rst.MoveNext
' j = j + 1
'Loop


'==================================
'ここから先は、終了処理
'==================================


'レコードセットと、接続を閉じる
rst.Close
cn.Close

'エクセルシート関係も保存して閉じる
'保存する時は、インデックスをちゃんと指定しましょう
xlapp.Workbooks(1).Save
xlapp.Workbooks.Close
'エクセルインスタンスを閉じる
xlapp.Quit

'各種インスタンスを開放
'（ちゃんと開放しないと、メモリーにゴミが残っちゃうよ)
Set rst = Nothing
Set cn = Nothing
Set mysht = Nothing
Set xlapp = Nothing

End Sub

---------------------------------------------------

(備考)
エラーが出現したら、エラートラップで、エクセルのインスタンスを終了しないとダメ。じゃなきゃ、エクセルの