シート一覧の入力画面

【環境】Windows 10 Pro 64bit、Excel for Microsoft 365

※「Office 365」は「Microsoft 365」に改名したそうですw

それはさておき、色々ありましたが、シート一覧の作成を進めていきます!

今回は、入力用シートである「ファイル一覧」シートを作成します

■シートを一覧する方法

シートの一覧を作成する場合、以下のやり方が多く一般的かと思います

広告

【指定したフォルダにあるExcelファイルの全てのシートを一覧する】

しかし、これでは特定のファイルのシートが見たい場合・・

以下のどちらかの作業が必要になってきますてきます

  • フォルダ内のファイルを全て調べなおす
  • ファイル1つをフォルダに移動する

そこで今回は、以下のステップで、シート一覧を作成します

  1. フォルダ内のファイル一覧を作成
  2. 任意のファイルを選択してシート一覧を作成

ベースは前回作成した「ファイル一覧」のツールです

■ファイル一覧

ファイル一覧をシート一覧の入力シートとして扱うために改良していきます

・改良点

仕様を考える際に記載した改良店は以下になります

※作業順に並び替えました

  1. 「一覧」シートの名前を「ファイル一覧」に変更
  2. 「カレントフォルダ情報」の参照を追加
  3. 「対象」項目の列を追加
  4. 「シート一覧取得」機能ボタンを追加
  5. 「ファイル名」項目にHyperLinkを設定
  6. 「シート一覧クリア」機能ボタンを追加

・作業開始

– 1.シート名変更

「ファイル一覧」と「シート一覧」を作成しますので、「一覧」では判りにくいため、シート名を「ファイル一覧」に変更します

広告

– 2.カレントフォルダ情報を追加

これは表紙の情報を参照しているだけです

必要か?と思われるでしょうが、必要です!(たぶん)w

・理由

【1つのシートで完結させること】

ただこれだけですw

そしてこの「カレントフォルダ情報」には、以下の2つの役割があります!

  1. 一覧を見やすくする
  2. シート一覧取得の入力情報

先に紹介した改良点のところの「改良前」の画像を見てください!

どこのサブフォルダなのか、どこのファイルか、それを知るためには、「表紙」シートを見ないと判らないのです

そのため、シート一覧を作成するにも情報として足りていない

そして、全てのファイルに共通する情報なので、全ての行に表示する必要もない情報です

それらを解消するには!?「カレントフォルダ情報」を追加することです

– 3.「対象」列を追加

これは、どのファイルのシート一覧を作成するのかを選ぶための列です

「○」を付けた行のファイルのシート一覧を作成します

画面を改良するのは簡単、1列(今回は最終列に)追加するだけ!w

問題はVBA側ですが、列を追加する際に、注意していることは、以下です

【他の列に影響を与えない】

当然ですし、単純なことですが、極めて大事なことなのです!!!

以下に2つのサンプルを紹介します(機能は同じです)

①お勧めできないソースサンプル

列番号を数字で直接指定しているため、新しい列を既存列の間に追加(挿入)した場合、他の列に影響が出てしまいます

番号を付けなおさないといけなくなります

これが複数の関数で行われていると、修正漏れも発生するかもしれませんし、テストしないといけない関数が多数出てくるかもしれません

②お勧めしたいソースサンプル

列の追加や挿入で他の列に影響を与えない方法としては、以下のサンプルのようなEnumを利用した列の指定方法です

番号の付けなおしが必要なくなります

Enumを修正して、必要な列の処理を追加するのみです!

③「対象」列を追加したサンプル

実際に「対象」列を追加したソースを見てみましょう!

修正したのは以下の2か所です

  • Enumに「対象」項目を追加
  • 「対象」に「○」を書き込む処理を追加

今回の変更では、大きな影響はでませんでしたが、既存列の間に複数の列を挿入する変更の場合には、Enumで列を指定しておくのがとめも便利だと言うことに気が付くと思います

是非ともお勧めしたい!(ぃゃ、常識なのか!?w)

– 4.「シート一覧取得」機能ボタンを追加

これは今回の新機能である「シート一覧」を作成するマクロを呼び出すためのボタンです

「シート一覧」を作成するために必要な情報は何かを考えます!

当然ですが、「対象ファイルパス」ですw

そのために、ツールにある情報で必要なものは何でしょうか?

  1. カレントフォルダ情報
  2. ファイル一覧情報
  3. 出力先シート情報

※出力シートは「その3」で説明します

ここでは、「カレントフォルダ情報」と「ファイル一覧情報」に着目します!

ボタン押下時のマクロ関数は以下のようになります

このマクロ関数は「Sheet2 (ファイル一覧)」に記載しています

そのため、サンプルに出てくる「Me」は「Sheet2」を表します

ちなみに、「Sheet3」は「Sheet3 (シート一覧)」です

①カレントフォルダ情報

「カレントフォルダ情報」は「ファイ一覧」シートに追加しましたので、ボタン押下した際に、セル情報を関数に渡して対応します

マクロ関数への引数の渡し方は以下を参照してください!

②ファイル一覧情報

「ファイル一覧情報」はCollection型を作成して、「ファイル一覧」シートから取得して、シート一覧を作成する関数に入力情報として渡します

この時、Collection型の中身は「ファイル一覧」クラスとします

プロジェクトにクラスモジュールを追加して、クラスのオブジェクト名を「clsFileInfo」とします

このクラスのサンプルソースは以下になります

あえて、「対象」項目をメンバに含めていません

つまり、このコレクションは「対象」のファイルのみをコレクションします

それは何故でしょうか?

理由は、シート一覧を作成する際に、参照するファイル一覧情報のデータ数を軽減することで、処理を高速化するためです!

そして、クラスには自分自身「Me」を返すGetプロパティを作成しています

これの使い方ついては、以下のファイル一覧情報取得のサンプルソースを見てください

ファイル一覧情報の「対象」行だけを取得して、dataコレクションを作成しています

「タイプ」「サイズ」「作成日時」「更新日時」は要らないんですけどねw

「With New clsFileInfo」でクラスを作成(オブジェクト化)しています

そのあと、各メンバを設定(シートから取得)し、最後に作成したGetプロパティ「Self」で作成したクラスをコレクションに追加しています

※「GetLastRow」や「GetTextCells」は不通に書くと行が長くなるので、まとめて関数にしていますので、その辺りはご自身で自由に書いてください

– 5.ファイル名にHyperLinkを付ける

これはファイル一覧を作成する際に行います

「対象」列を追加で記載した③のサンプルに、以下のように「Hyperlinks.Add」メソッドを使って、「ファイル名」にファイルリンクを付ける一行を追加します

– 6.クリアボタンを追加

「ファイル一覧」シートにクリアボタンを作ると、「ファイル一覧」がクリアされると思ってしまうので、長いですが、「シート一覧クリア」ボタンを作成します

単純にRangeで範囲を指定して、「ClearContents」メソッドで一覧を削除します

■次回

入力シートから取得した情報を基に、実際にシート一覧を取得して、シート一覧を作成するところを次回記載します

今回はここまで!

ではでは

シェア
広告

やもす ʕ•͡-•ʔ

のんびり!のほほん!がモットーです!w 蕎麦食いたい ライブ行きたい 暑いの嫌い