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

きなこSHOW

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

AccessからExcelテンプレートをひな形にして新規Workbookを作成するVBAサンプル

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

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

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

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

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

その原因は、シートを使いまわししているからなのでは?と私は推測しています。
見えないシングルクォートみたいなものがセルに残っているのではないかと。
対策としては、前に出力したExcel Bookに上書きでデータ出力する方法をやめる=シートを使いまわさずに、新規作成するのが書式トラブルを回避する秘訣。と考えています。

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

ExcelテンプレートをCopyして新規Sheetを作成するサンプル

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

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

上から順を追って説明します。
(1) Template のパスとファイル名を引数で受け取って、まずファイルが存在するかチェックします。
(2) 存在する場合、テンプレートファイルを開きます。
(3) 開いたテンプレートBook内のシートに、第4引数で渡されたシート名と一致するものがあるか、Worksheetsコレクションをループして探します。
(4) 指定されたシートが見つからない場合、エラーメッセージを返してプロシージャを抜けます。
(5) 指定されたシートが見つかった場合、そのシートを変数にSetします。
(ここで新規Worksheetが作成されます。)
(6) 第5引数の出力先BookがNothingの場合、出力先の新規Bookを作成します。
(7) Templateの指定なしでこのプロシージャが呼ばれた場合、新規Worksheetを作成します。
(8) 第5引数に出力先Bookが指定されている場合(=すでに存在するBookに新規シートを追加したい場合)、出力先Bookの末尾に作成されたWorksheetを作成します。

上記コードで、テンプレートありの場合と無しの場合、出力先Bookが存在する場合と指定なしで新規Bookを作成する場合の全パターンをカバーしています。

次にこのプロシージャを実行するコードを紹介します。

呼び出し例

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

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

ちなみにひな形にできるのは、Excelテンプレ―ト限定ではなく、拡張子「xlsx」の普通のExcel Bookや古い「xls」でも大丈夫ですし、テンプレの指定をしなければ空のシートを作る仕組みになっています。

Excelから呼び出すサンプル

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

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

 

いつか、お役に立てれば幸いです。