【EXCEL:VBA】フォルダに存在する全てのEXCELシートを開きたい

フォルダに存在する全てのEXCELシートを開きたい

この記事では、指定されたフォルダに存在するEXCELファイルの全シート名を取得し、シートを簡単に開くことができるようにハイパーリンクを作成する方法について説明しています。

 

VBAを利用すれば、フォルダに存在する全てのファイル名を取得することができます。

 

また、EXCELファイルの各シートを開くためのハイパーリンクを作成します。

 

ハイパーリンクをクリックするだけで、各シートを自動的に開くことができます。

 

しかも、全てのシート名を間違いなく取得し、簡単に一覧化することができます。

 

 

それでは、指定したフォルダ配下の全てのEXCELファイルを対象に、全てのシート名のハイパーリンクを設定する機能を作成していきましょう。

 

困っている女性

大量に作成されているEXCELファイルに存在するシートを1つ1つ開いて確認するのが地味に大変なの・・・、わかる!?

困っている女性

仕事でたくさんEXCELファイルを作成するので、あのシートはどのEXCELファイルに存在するか、探すことがよくあるの。

困っている女性

そういう時に、1つ1つEXCELファイルを開いて目的のシートを確認するのが大変なの・・・。
それなら、VBAを使えばボタン1つ押すだけで、大量のEXCELファイルから目的のシートをみつけることができ、さらに簡単に開くことができるようになるよ。

解決する男性

フォルダパスを指定すれば、そのフォルダに存在するEXCELファイルの全シートを開くハイパーリンクを一気に作成できるよ。

解決する男性

困っている女性

それそれ、早く教えてよ。

 

EXCELファイルの全シートを開くハイパーリンクを一気に作成する機能を、1つ1つ順番に説明していきます。

フォルダ指定欄、シート名リスト表示欄を準備する

まず、フォルダを指定する欄とそのフォルダに存在するEXCELファイル名とシート名を、表示させる欄を作成します。

「メイン」シートを作成する

メインシートを作成する

メインシートを作成する

 

  1. 「メイン」という名前のシートを作成します。
  2. 1行目に「フォルダパス」のタイトル行を作成します。
  3. 2行目に「フォルダパス」を入力する欄を作成します。
  4. 3行目に「ファイル一覧」のタイトル行を作成します。
  5. 4行目に「ファイル名」、「シート名」のタイトル行を作成します。
  6. 5行目以降は、指定されたフォルダに存在するEXCELファイル名とシート名が表示される欄になります。

 

5行目以降の「ファイル名」、「シート名」はVBAプログラムが記入してくれます。

また、「シート名」にはハイパーリンクが設定され、そのハイパーリンクをクリックすると、そのEXCELファイルのそのシートを開くことができます。

 

VBAでシート名を取得する機能を記入する

開発タブの「Visual Basic」をクリックして、「Microsoft Visual Basic for Applications」を起動します。

Microsoft Visual Basic for Applicationsを起動する

Microsoft Visual Basic for Applicationsを起動する

 

「Microsoft Visual Basic for Applications」にVBAプログラムを入力します。

VBAプログラムを入力する(その1)

VBAプログラムを入力する(その1)

 

VBAプログラムを入力する(その2)

VBAプログラムを入力する(その2)

 

標準モジュールを追加して、以下のソースを書きます。

 

最後にEXCELを「◯◯◯.xlsm」というような感じでマクロ有効ブック形式で保存します。

 

EXCELファイルの全シートにハイパーリンクを作成する機能の使い方

それでは、指定したフォルダに存在するEXCELファイルの全シートにハイパーリンクを作成する方法を説明します。

 

まず、A2セルにEXCELファイルが存在するフォルダパスを入力します。

 

次に、「開発」タブの「マクロ」をクリックします。

VBAプログラムを実行する

VBAプログラムを実行する

 

先程作成したVBAプログラム「Main_Proc」が表示されていますので、選択します。

次に、「実行」ボタンをクリックしてファイル名とシート名を取得します。

実行ボタンをクリックする

実行ボタンをクリックする

 

しばらく待つと、指定したフォルダ配下に存在する全EXCELファイル名とシート名が5行目以降に書かれます。

全てのEXCELシートを開くハイパーリンクが作成された

全てのEXCELシートを開くハイパーリンクが作成された

 

A列には、EXCELファイル名が書かれます。

B列には、A列のEXCELファイルに存在するシート名が書かれ、ハイパーリンクが設定されます。

ハイパーリンクをクリックすると、そのEXCELシートが表示されます。

ハイパーリンクをクリックして、EXCELシートを開く

ハイパーリンクをクリックして、EXCELシートを開く

 

ちなみに、実際のフォルダは以下のようになっています。

実際のフォルダの様子

実際のフォルダの様子

 

シート名を調べるVBAプログラム(Main_Proc)の説明

それでは、VBAプログラム(Main_Proc)を説明します。

①参照するシートを変数に格納する

ここでは、このあとの処理で何度も使う「メイン」シートを変数に格納しています。

そうすることにより、入力するプログラムを短くすることができるからです。

何度も「ThisWorkbook.Sheets(“メイン”)」と入力するより、「shtMain」と入力するほうがコードが少なくてよいからです。

 

②フォルダパスを変数に格納する

ここでは、このあとの処理で何度も使う「フォルダパス」を変数に格納しています。

 

③データ入力されているA列の最終行を取得する

ここでは、A列の入力されている最終行を変数に格納しています。

何度も実行すると、前回実行時のEXCELファイル名が入力されているので、

処理の実行前にセルに入力されている値をクリアするために、

値が入力されている最終行を取得します。

最終行を取得することで、どこまでのセルをクリアするべきかがわかるからです。

 

④最終行が5行目以降なら、5行目以降のA列とB列のセルをクリアする

ここでは、先程取得した最終行が5以上なら、セルの値をクリアする必要があります。

その場合、A5セルからB列の最終行までをクリアします。

この処理によって、すでに入力されているファイル名とシート名がクリアされます。

 

⑤FileSystemObjectを変数に格納する

ここでは、「FileSystemObject」を変数に格納しています。

「FileSystemObject」は、フォルダに存在するファイルを調べることに利用します。

 

⑥指定されているフォルダに存在するファイル数分処理を繰り返す

ここでは、指定されたフォルダパスに存在するファイル数分、以降の処理を繰り返します。

 

⑦拡張子がxlsxのファイルのみ対象とする

ここでは、フォルダに存在するファイルの拡張子が「xlsx」か確認しています。

拡張子が「xlsx」のファイルのみを対象に、以降の処理を行います。

例えば、拡張子が「txt」なら、対象外となります。

また、LCase関数を利用して、取得した拡張子を小文字に変更しています。

取得した拡張子が「XLSX」のように大文字だった場合、対象としたいが対象とならないからです。

 

⑧EXCELファイルを開く

ここでは、フォルダに存在するEXCELファイルを開いています。

「f.Path」で該当のEXCELファイルのフルパスを取得できます。

 

⑨存在するシート数分繰り返す

ここでは、開いたEXCELファイルに存在するシート数分、以降の処理を繰り返します。

 

⑩現在行を次の行に変更する

ここでは、「メイン」シートに書き込みする行を次の行に変更しています。

 

⑪ファイル名をA列にセットする

ここでは、取得したEXCELファイル名を現在行のA列に記入しています。

「f.Name」で、EXCELファイル名を取得できます。

 

⑫シート名をB列にセットする

ここでは、取得したシート名を現在行のB列に記入しています。

 

⑬シートを開くハイパーリンクを設定する

ここでは、現在行のB列にハイパーリンクを設定しています。

そのハイパーリンクは、現在行のEXCELファイルのEXCELシートのA1セルを指定しています。

 

⑭EXCELファイルを閉じる

ここでは、開いたEXCELファイルを閉じています。

1つのEXCELファイルに存在するシート名を「メイン」シートに記入し終わると、EXCELファイルを閉じます。

 

⑮ワークブックをクリアする

ここでは、ワークブックオブジェクトをクリアしています。

 

⑯FileSystemObjectをクリアする

ここでは、使い終わったFileSystemObjectをクリアしています。

 

結果として、指定されたフォルダに存在するEXCELファイル全ての全シートを一覧化できるようになりました。

また、各シートにはハイパーリンクが設定されており、そのハイパーリンクをクリックすると、該当のEXCELシートを開くことができます。

 

それでは、各シートを簡単に開くハイパーリンク一覧を作成するツールを作って、作業を効率化しましょう。

 

以上です。

 

 

最後まで読んでいただきありがとうございました。
この記事をシェアしていただけると喜びます。

 

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です