【VBA】Access2007からEXCEL操作しようとしたときの落とし穴メモ

Accessで集計したデータを少し凝ったレイアウトのEXCELにして表示したかったので、EXCEL VBAを使って出力しようとしたのだが、幾つか落とし穴があったので、同じ問題で悩まないようここに記載しておく。

ちなみに毎度書くけど、俺は３流プログラマなのでソースにはバグや間違いがあるかもしれない。
利用は自己責任で。

【やりたいこと】
Accessで集計したデータをEXCELに出力して表示したい。
EXCELのレイアウトはやや複雑で、Accessのテーブルの内容をそのままExportというわけにはいかない。

【環境】
Win7 32bit
Office2007（ACCESS2007、EXCEL2007）

【前提条件】
参照設定で「Microsoft EXCEL XX.X Object Library」を参照していること。
※、XX.Xは任意のバージョン番号。俺は12.0だった。


Public Declare Function ShowWindow Lib "user32" (ByVal hwnd As Long, _
    ByVal nCmdShow As Long) As Long

Private Sub CreateNewWorkbook()
On Error GoTo Errhandler:

    'EXCEL用オブジェクト
    Dim objEXE As Excel.Application
    Dim objWB As Excel.Workbook
    Dim objWS As Worksheet

    'EXCELオブジェクト生成
    Set objEXE = CreateObject("Excel.Application") 'Set objEXE = Excel.Application と書いている情報も存在するが、その場合俺の環境では、タスクマネージャのプロセスに「EXCEL.EXE」が１つも存在しない場合、「リモートサーバはないか、使用できるる状態ではありません。」というエラーになったので、最終的にこの形に落ち着いた。

    'EXCEL操作したときに確認ダイアログが表示されないように
    objEXE.DisplayAlerts = False

    '新規ワークブック追加
    Set objWB = objEXE.Workbooks.Add

    '先頭シート以外は削除（今回は１シートだけあればいいので）
    Do While objWB.Worksheets.Count > 1
        objWB.Worksheets(objWB.Worksheets.Count).Delete
    Loop

    '先頭シート取得
    Set objWS = objWB.Worksheets(1)

    'シートに値を設定したり、書式や関数を設定したりする。
    'EXCEL VBA と同じなので、ここは割愛


    '先頭シートをアクティブに
    objWB.Worksheets(1).Activate
    
    'シートのスクロールをトップ位置に設定
    objEXE.ActiveWindow.ScrollColumn = 1
    objEXE.ActiveWindow.ScrollRow = 1

    '先頭セルを選択状態に
    objWB.Worksheets(1).Cells(1, 1).Select

    'EXCELファイル表示
    objEXE.Visible = True

    'EXCELファイルのウィンドウを最前面に
    ShowWindow objEXE.hwnd, 1

    '確認ダイアログの無効化解除
    objEXE.DisplayAlerts = True

    '処理終了
    Exit Sub

Errhandler:

    MsgBox Err.Description

    'EXCELが裏で開きっぱなしになる状態を避ける
    If Not objEXE Is Nothing Then

        'EXCEL終了
        objEXE.Quit

        '確認ダイアログの無効化解除
        objEXE.DisplayAlerts = True

    End If

End Sub

実際に作成したソースではテンプレートファイルを用意してそこからシートをコピーしたりと色々やってるが、上記ソースはその中で今回の記事に最低限必要な部分のみ抽出した。

ちなみに、EXCELのセル１つひとつに値を埋め込んでいくと超遅くてキレそうになる。
もし、埋め込み対象のセルの位置が固まってるなら



    '数字でも文字でも日付でも格納可能なようにVariantで配列を宣言
    '（データの種類が１種類だけなら、その型で配列にすればベター）
    Dim cellValues() As Variant

    '必要に応じて配列をリサイズ
    ReDim cellValues(1 To 10, 1 To 5)

    '配列に値を設定
    cellValues(1, 1) = "あいうえお"
    cellValues(1, 2) = 100
    cellValues(1, 3) = CDate("2013/7/24")
    ・・・以下略

    'まとめて貼付
    objWS.Range("B1:F10") = cellValues

とした方が高速に処理できる。（参考サイト）

超キレやすいプログラマのブログ

キレてキレてキレまくる

【VBA】Access2007からEXCEL操作しようとしたときの落とし穴メモ