人事では評価の収集など、各管理職の方に社員のリストを送付することがしばしばあります。

その際、各組織ごとのファイルをフォルダに格納し、入力してもらっています。

この「各組織ごとのファイルを集めるツール」が今回ご紹介するツールとなります。

 

ファイル収集ツールの概要

同じレイアウトのファイルを一つのファイルに集約することのできるツールです。

 

嵐のファイルは以下の通り

 

SMAPのファイルは以下の通り

 

TOKIOのファイルは以下の通り

 

ご覧いただいてわかる通り、すべてのファイルが同じレイアウトをしています。このようなファイルが複数ある場合、今回のツールを利用することが可能です。

 

実際に実行した結果が次の画面です。このように、複数のファイルを「Merge」シートに収集することが可能です。（列の幅は取り込んだ後に調整ください）

 

ダウンロードリンク

以下のファイルをダウンロードしてご利用ください。

 
 

ファイル収集ツールの使い方

１．Settingシートの「対象フォルダパス」を入力する

C3：対象フォルダパスのセルに収集したいファイルが格納されているフォルダパスを入力します。複数のフォルダに別々にファイルが存在する場合は、その親フォルダのフォルダパスを指定することで、サブフォルダまでファイルを収集することができます。

 

なお、フォルダパスの最後に「￥」が入っていてもいなくても構いません。

２．Settingシートの「対象ファイルの拡張子」を入力する

C4：対象ファイルの拡張子のセルには、収集するファイルの拡張子を入力します。

３．Settingシートの「データ開始行」を入力する

C5：データ開始行のセルには、収集するファイル内のデータが入力されている最上の行番号を入力します。今回のテストファイルはヘッダーが３行目なので、４行目からデータが入っています。

 

したがって、データ開始行には「４」を入力します。

 

５．Settingシートの「試行回数」を入力する

C6：試行回数のセルに数値を入力すれば、その回数だけ実行することができます。このツールを利用する場合、たくさんのファイルを収集することが想定されます。

手戻りを発生させないためにも、数ファイルだけ収集を行い、これまでの設定内容が間違っていないかどうかを確認することをおすすめします。

 

６．Settingシートの「対象シート名」を入力する

C8：対象シート名が決まっている場合は、こちらのセルにデータを入力します。仮に１シートしかない場合は特段の設定は不要です。

 

同一ファイル内に対象シート名が一致するシートが複数ある場合

Ver2.0にて新機能「同一ファイル内に対象シート名が一致するシートが複数ある場合、すべてのシートを収集する機能」を追加しました。

対象シート名に以下のように「情報」と入力しています。

 

収集するファイルの中に入力した「情報」と一致するシートがあれば、すべてのシートを収集します。

その際、もちろんシートのレイアウトは同じである必要があります。

（シート名は「情報」で部分一致させます。完全一致で収集したいというニーズがあれば教えてください）

 

７．Settingシートの「対象ファイル名(部分一致)」を入力する

Ver1.2にて新機能を追加しました。たくさんのファイルが格納されているケースを想定し、一部のファイル名が合致する場合に収集する機能を追加しています。

部分一致で検索を行いますので、

混老頭_人事情報.xlsx　や　人事の混老頭情報.xlsx　といった具合で、混老頭を含むファイル名だけを表示します。

 

ブランクの場合は、もちろんすべてのファイルを収集します。状況に応じて使い分けてください。

 

８．Settingシートの「シート保護時のパスワード」と「ブック保護時のパスワード」を入力する

ファイル自体にパスワードが設定されている場合はセルC10にパスワードを入力します。

シートに保護のパスワードが設定されている場合はC9にパスワードを入力します。

 

パスワードが設定されていないことがほとんどだと思いますので、オプション機能として考えていただければと思います。

 

９．「ファイル収集 実行」ボタンをクリックする

８．までで設定終了です。

あとは、「ファイル収集 実行」ボタンをクリックしましょう。すぐに実行が始まります。

 

ファイルの実行中は左下に収集状況が表示されます。

９．実行完了後、「Result」シートと「Merge」シートを確認する

収集が完了したら、以下のようなメッセージボックスが表示されます。

 

今回の例では、ファイル収集ツールを取り込む３ファイルと同じフォルダに配置して実行してみました。マクロを実行している本ツールは対象外として取り込みからは除外されるようになっています。

 

以下は「Result」シートの内容です。対象外のファイルがグレーアウトされていることがわかります。

 

各列の解説は以下となります。

 

  • コピー開始行：「Merge」シートに転記する際の開始行
  • 収集した行数：元ファイルからコピーした件数
  • コピー元ファイル：収集したファイルのパスを表示（クリックするとそのファイルを開くことができます）
  • シート名：コピー元ファイルの取り込んだシート名が表示されます。
  • コピー先(Mergeシートへのリンク)：「Merge」シートに貼り付けた先頭行へのリンク

 

実行後は「Result」シートに移動するようになっています。

「Merge」シートに移動してみましょう。一部の列幅が小さくなっていることがわかります。

 

列幅はコピー時に自動で変更されません。そのため、手動で調整をお願いします。

よくあるエラー

「ファイル収集ツール」という同名のファイルを収集する対象のフォルダに格納していると、同じファイル名が開けないという原因でエラーとなります。Excelあるあるです。

 

さいごに

既に更新もしていっていますが、このツールを覚えておくと、かならずどこかで使えると思います

現に私自身いまだに使っているツールです。

 

更新履歴

バージョン 内容 更新日
1.0 初版リリース 2020/12/08
1.1 収集するファイルに外部リンクがある際に、メッセージが表示されていたため、アラートを非表示に変更 2021/03/17
1.2 収集するファイルのファイル名を部分一致で検索するオプションを追加 2021/04/29
2.0 指定したシート名について、収集ファイル内の複数シート名が一致する場合、すべてのシートを収集するように変更 2021/12/10
2.1 非表示シートを取り込むことのできない不具合を解消 2023/01/04
2.2 シートが”パスワードが設定されていないが、保護だけされている状態”の時にエラーとなる事象を確認。修正。 2023/07/12