Access VBAでExcelを操作する方法をお探しですね。

広告

AccessとExcelをVBAで自動連携！面倒な作業をボタン一つで終わらせよう

仕事でAccessとExcelを両方使っていると、「AccessのデータをExcelに出力して加工する」とか、「Excelで作った表をAccessに取り込む」といった作業、けっこう頻繁にありますよね。

手作業でコピー&ペーストしたり、インポート機能を使ったりするのは簡単ですが、毎日となるとかなり面倒。

しかも、うっかりミスでデータが壊れてしまう危険もあります。

そこで役に立つのが、VBA（Visual Basic for Applications）を使った自動化です。

VBAを使えば、AccessからExcelを「裏側で」操作して、複雑な帳票作りやデータのやり取りをボタン一つで完結させることができます。

この記事では、実務ですぐに使えるAccess VBAによるExcel操作の基本から、ファイルを簡単に選べる「ファイルダイアログ」の作り方まで、わかりやすく解説していきます。

まずは準備！参照設定をしよう

AccessからExcelという「別のアプリ」を操作するには、まずAccess側に「Excelの機能を使うための辞書」を持たせる必要があります。

これを「参照設定」といいます。

やり方は簡単。

VBAのコードウィンドウ（VBE）を開いて、メニューの「ツール」から「参照設定」を選びます。

表示される一覧の中から「Microsoft Excel X.X Object Library」（X.Xの部分はバージョンによって違います）を探してチェックを入れるだけ。

これで、Access内でExcel専用の命令が使えるようになります。

参照設定をすると何がいいかというと、コードを書くときに自動で候補が表示される機能（インテリセンス）が使えるようになるんです。

たとえば「Ex」と入力するだけで「Excel」に関連する言葉が出てくるので、スペルミスを防げるし、開発スピードもグッと上がります。

実行速度も少し速くなるというおまけ付きです。

ただし、配布先のPCでExcelのバージョンが違う可能性がある場合は、参照設定を使わずに「CreateObject関数」を使う方法もあります。

でも、まずは開発しやすい参照設定から始めるのがおすすめです。

ファイルを選びやすく！ダイアログの作り方

AccessからExcelファイルを操作するとき、プログラムの中に「C:\Data\売上.xlsx」みたいにファイルの場所を直接書いちゃうと、ファイルを移動したりフォルダ名を変えたりしたときにエラーになってしまいます。

それに、ユーザー自身に処理したいファイルを選んでもらいたいこともありますよね。

そんなときに必要なのが、Windowsでおなじみのファイル選択画面を表示する「ファイルダイアログ」です。

これを使えば、ユーザーは普段の操作と同じ感覚で、処理したいExcelファイルを選べるようになります。

VBAでファイルダイアログを使うには、`Application.FileDialog`というメソッドを使います。

引数に`msoFileDialogFilePicker`を指定すると、ファイル選択用のダイアログが表示されます。

このダイアログは、タイトルを変えたり、最初に表示するフォルダを指定したり、「Excelファイル（*.xlsx）だけ」に絞り込んだりと、いろいろカスタマイズできます。

ユーザーが「キャンセル」ボタンを押したときの処理もちゃんと書いておけば、変なエラーでプログラムが止まることもなく、業務アプリとしての質がグッと上がります。

Excelを起動してデータを操作しよう

ファイルのパスが取得できたら、いよいよAccessからExcelを起動して操作していきます。

基本的な流れは、「Excelアプリを起動」→「ブックを開く」→「シートを指定」→「セルを操作」という順番です。

具体的には、`New Excel.Application`でExcelを起動して、`Workbooks.Open`で指定したファイルを開きます。

このとき、処理中のExcel画面をユーザーに見せるかどうかは`Visible`プロパティで決められます。

テスト中はTrueにして動きを確認して、本番ではFalseにしてバックグラウンドで処理すると速くなります。

データを書き込んだり読み取ったりするときは、「どのブックの、どのシートの、どのセル」なのかをはっきり指定することが大事です。

単に`Cells(1, 1)`と書くと、意図しない場所を参照してしまうことがあるので、シートオブジェクトをちゃんと変数で定義して、`ws.Cells(1, 1).Value = “テスト”`みたいに書きましょう。

AccessのテーブルやクエリのデータをExcelに転記する場合、1セルずつループで書き込むとすごく時間がかかります。

そんなときは`CopyFromRecordset`メソッドを使うのがコツ。

これなら数万件のデータでも一瞬でExcelに貼り付けられて、作業効率が劇的にアップします。

後片付けを忘れずに！メモリリークを防ごう

他のアプリを連携させる自動化で一番気をつけなきゃいけないのが「後片付け」です。

VBAで作ったExcelのオブジェクト（Application、Workbook、Worksheetなど）は、処理が終わったらちゃんと破棄しないと、Accessを閉じてもPCのメモリに「見えないExcel（ゴーストプロセス）」が残り続けてしまいます。

これが溜まるとPCが重くなったり、次にファイルが開けなくなったりするトラブルの元になります。

これを防ぐには、コードの最後で必ず次の手順を踏みましょう。

* 開いたブックを保存（Save）するか、保存せずに閉じる（Close）
* 起動したExcelアプリを終了させる（Quit）
* 使ったすべてのオブジェクト変数に「Nothing」を代入してメモリを解放する

それから、処理の途中でエラーが起きたときでも確実に後片付けができるように、エラーハンドリング（`On Error GoTo`など）を組み込むのも大切です。

正常に終わったときだけじゃなく、エラーで止まったときも必ず「お片付け」が実行されるようにしておけば、安定して動き続けるシステムが作れます。

ちょっとした手間ですが、この後片付けをきちんとやっておくと、長く使えるプログラムになりますよ！

広告