読者です 読者をやめる 読者になる 読者になる

きなこSHOW

どうやったら寝たまま仕事ができるかあれこれ考える。

AccessからExcelの新規シートを作成するVBAサンプルを紹介

Access VBA

AccessからExcelにデータを出力する場合、エクスポート機能を使うケースが多いのかなと思います。

Excel側のレイアウトがどうでもいいときは、OutputToか、トランスファーなんちゃらで良いのですが、レイアウトが作りこまれているExcelシートにデータ出力したいときは、Excelテンプレートから新規Worksheetを作りましょう。

新規シートを自動で作るサンプルコードを紹介します。

Excelのトラブルの原因は、シートの使いまわしなのでは?

例えばですけど、VLOOKUPの式は正しいのにエラーになってしまうとか値が表示されないとか、F2でセル内編集状態にしてEnterキーたたいたら正しく表示されたとか...
職場でよく聞く話です。

その原因は、Worksheetを使いまわししているからなのでは?と私は思っています。
見えないシングルクォートみたいなものがセルに残っているのではないかと。

だから、Worksheetは使いまわさず、新規作成するのがトラブル回避の秘訣です。

これから紹介するコードは、Excelのテンプレートを元に新規Worksheetを作成します。
そこにデータを出力する処理はまた後日にでも紹介するとして、まずはWorksheetを作成するところの自動化から。

Excelテンプレートから新規Sheetを作成するサンプル

Accessで新規標準モジュールを用意して以下のプロシージャを貼り付けます。

Excelの新規シートを作るプロシージャ

上から順を追って説明します。
Template のパスとファイル名を引数で受け取って、まずファイルが存在するかチェックします。
存在する場合、テンプレートファイルを開いて、次の引数で渡されたシート名がそのテンプレート内にあるか、ループしながら探します。
指定されたシートがあれば、そのシートをテンプレートにして新規Worksheetを作成。
なければエラーメッセージを返します。
Templateの指定なしでこのプロシージャが呼ばれた場合は、新規Excel Bookを作成。
といった事をこのコードでやっています。

このコードを実際に動かすための準備として、Excelのテンプレートをなんでも良いので用意してください。
テンプレートがあると新規作成されたって見た目でわかりやすいので。

ご自分で用意しても良いですし、テスト用にMicrosoftOfficeの公式サイトからテンプレートを頂戴するのも良いでしょう。
無料テンプレート公開中 - Microsoft Office - 楽しもう Officeライフ

呼び出し例

次のコードはExcelのテンプレート「受領書31.xltx」をひな形に新規作成するサンプルです。

サンプルコードを解説しますと、
Templateのファイル名とシート名を引数用の変数にセットして、あとで保存するときに付ける用のファイル名を作成しておきます。
次に、Excel Applicationオブジェクトを新規作成して、Excel新規シート作成関数を実行します。何らかのエラーが発生したときは以降の処理は行わずに抜けています。
新シートの作成が正常に終了したら、ファイル名を付けて保存。
生成したExcel Applicationを終了して、変数を解放しています。

ちなみに、ひな形にできるのはExcelのテンプレ―ト限定ではなく、拡張子「xlsx」の普通のExcel Bookでも大丈夫ですし、テンプレ無しでも空のシートができるので大丈夫。

Excelから呼び出すサンプル

呼び出しサンプルコードの「CurrentProject.Path」を「ThisWorkbook.Path」に書き換えると、このモジュールをExcelからも実行することができます。

この場合、Excel.Applicationは省いても良いでしょう。

さいごに

ご自身でたくさんテストしてから使ってください。
何かあっても責任はとれませんよ!

それではまた!