エクセルVBAでThisWorkbook.Pathによるフルパスを定数っぽく使えるようにする方法

★気に入ったらシェアをお願いします!


auto_open

photo credit: Hugo-90, 35 million views XKE via photopin (license)

みなさん、こんにちは!
タカハシ(@ntakahashi0505)です。

エクセルVBAでのThisWorkbook.Pathはかなり使用頻度が高いと思うのですが、変更したいとき、けっこう面倒なんですよね。

プロジェクトの全てのコードから該当の「ThisWorkbook.Path & “\” & ファイル名」を探し出してフルパスに書き換えて…ってちょっと危なっかしい作業になります。

そんな時には、エクセルファイルを開いたときにそのファイルのファイル名フルパスを定数(的な変数)に放り込めれば便利です。

ということで、今回はエクセルVBAでThisWorkbook.Pathを使ったファイル名フルパスを定数っぽく使えるようにする方法です。

では行ってみましょう!

ThisWorkbook.Pathを使ったフルパスの指定で困ること

エクセルVBAでお仕事用のツールやシステムを作っていると、プログラムを記述しているエクセルファイルだけでなく、他のファイルも含めて複数のファイルを同じフォルダに詰め込んで、一つのシステムを構成したいときがあります。

例えば、Accessデータベースファイルと読み書きする場合とか

【エクセルVBA&Access連携】SQL文でデータを抽出する最も簡単なプログラム
エクセルVBAでAccessデータベースを操作する方法についてシリーズでお伝えしています。今回はデータベース言語SQLとADODB.Recordsetオブジェクトでデータベースからデータを抽出します。

他のExcelファイルやPowerPointファイルをひな形的に活用する場合とか

エクセルVBAでPowerPointのプレゼンテーションを開く方法・スライドを取得する方法
エクセルVBAでPowerPointを操作する方法をシリーズでお伝えしています。今回は特定のPowerPointプレゼンテーションファイルを開くプログラムおよびスライドを取得するプログラムです。

マクロを記録してるファイル以外のファイルを使いたいときは多いものです。

その場合、プログラムを記述しているエクセルファイルと同じ階層にファイルがありますからThisWorkbook.Pathを使うことで、他の構成ファイルを開いたり操作したりできますよね。

ThisWorkbook.Path & “\” & ファイル名

と書くのですが、あちこちのプロシージャに何回もそうやって書いておくと、後で

  • 「やっぱり固定パスにしよう」
  • 「ファイル名変えよう」

など、変更になったときに全部を書き換えなければなりません。

作業としては、プロジェクトの全てのコードから該当の「ThisWorkbook.Path & “\” & ファイル名」を探し出して、フルパスに書き換える作業になりますが

  • どこか一か所を変更し忘れる
  • どこか一か所をスペルミスする

などの可能性があります。

VBEの機能では一斉置換などもありますが、ちょっぴりおっかないので、できればこの手で変更したいですしね。

ThisWorkbook.Pathは定数に代入できない

それが嫌だからといって、プロシージャ外に

などと書いても

エクセルVBAのエラー「定数式が必要です」

「コンパイルエラー:定数式が必要です。」というエラーが出て怒られてしまいます。

それはそうです、定数ですから「”hoge”」とか「123」といった固定の値でなければなりません。これはプロシージャ内でやってもConstである限り同じこと。

Auto_OpenイベントでPublic変数にフルパスを格納する

そんな時に使えるアイデアがこちら。

Auto_OpenイベントでPublic変数にThisWorkbook.Pathをいつも格納してしまう

というアイデアです。

つまり

  1. マクロを書いたエクセルファイルを開くとAuto_Openイベントが走る
  2. Auto_Openイベントプロシージャ内でPublic変数にThisWorkbook.Path(またはそれを使ったファイル名フルパス)を代入

という段取りが、ファイルを開くたびに自動で動くようにするのです。

実際にはこう書いておけばOKです。

ちなみに、Auto_Openは標準モジュールに書いておいてくださいね。Auto_Openは標準モジュールに書く必要がありますので。

標準モジュールの一番上に、Public変数の宣言とAuto_Openをセットで並べて記述しておくのがミソです。

フルパスを変更したいときも簡単

こうしておけば、うっかり該当のファイルを固定パスで指定したくなったときも、簡単に対応できます。

標準モジュールの一番上のところだけを

このようにちょちょいっとコメントアウト(もちろん削除してもOK)しつつ、定数に書き換えれば、全プロジェクトに対しての対応が一か所の修正で可能になります。

ちなみに、当然ですがAuto_Openプロシージャをコメントアウトまたは削除しておかないと

エクセルVBAのエラー「定数には値を代入できません」

このように「コンパイルエラー:定数には値を代入できません。」と怒られます。

だから、Auto_Openプロシージャを標準モジュールの一番上に記載しておくということですね。

まとめ

ということで、エクセルVBAでThisWorkbook.Pathを使ったファイル名フルパスを定数っぽく使えるようにする方法についてお伝えしました。

Auto_Openプロシージャ、覚えておくと便利ですね。以下のような用途でも使えますしね。

エクセルVBAでブックを開いたときに他のワークブックも一緒に自動で開く
エクセルで特定のファイルを開いたら、一緒に使用する他のファイルも自動で開くと便利です。今回はAuto_Openというイベントプロシージャを使ってブック起動時に他のブックも自動で開くVBAプログラムです。

ちょっと大きめのプロジェクトになると、けっこうファイルパスがアチコチに書き込まれることになるので、今回のように一か所で更新できるようにしておくと、スッキリしますしメンテナンスが楽になります。

ぜひ、ご活用下さいね!