サブフォルダ含むすべてのフォルダの Excel を文字検索するマクロ!
ある日仕事で「フォルダの Excel ファイルからこのキーワード探しといて~」と言われて文字検索しまくった経験ありませんか?

 

そんなことしたら日が暮れるわ!!!

そう思いながら Excel ファイルをひとつひとつ探した。

そんなつらい思いをされた方もいるかもしれません。

 

また Excel ファイルというのは、外部ツール(たとえばサクラエディタの grep 機能など)で検索できません。

そんな状況で大量の Excel ファイル から文字検索するの~~~

 

ですが、そんな悩みも今日で解消です。

この度、サブフォルダ含むすべてのフォルダの Excel を文字検索するマクロを作成しました!!

 

 

 

このページではサブフォルダ含むすべてのフォルダの Excel を文字検索するマクロの使い方と作成方法についてご紹介します。

もしよければ参考にしてみてください。

※ いつものようにつくったのも共有しときますので、よければどうぞ。

 

 

スポンサーリンク

「サブフォルダ含むすべてのフォルダの Excel を文字検索するマクロ」の使い方

「サブフォルダ含むすべてのフォルダの Excel を文字検索するマクロ」の使い方は下記になります。

 

  1. 検索ワードを入力する
  2. 検索ボタンを押下する
  3. フォルダダイアログが表示されるので、検索したいフォルダを選択する

 

これだけです。それでは実際にやってみるとします。

まず検索ワードを B3 セルになんでもいいのでいれます。

この時入力し忘れていれば、後の検索処理できないようになっています。

 

 

そして検索ボタンを押下します。

 

 

するとフォルダダイアログが表示されます。

ここでは検索先のフォルダを選択してOKをクリックします。

 

 

検索処理が実行され、検索結果が表示されます。

 

 

ここで「サブフォルダ含むすべてのフォルダの Excel を文字検索するマクロ」の仕様について書いておきます。

 

仕様
  1. この検索処理は「.xls*」の拡張子がついているファイルに対して実行されます。
  2. Excel ファイルに保護がかかっている、もしくは非表示シートがある場合もその処理は検索対象となります。
  3. パスワード保護がかかっている Excel ファイルは読み込まれません。

 

「サブフォルダ含むすべてのフォルダの Excel を文字検索するマクロ」の作り方

ここからプログラミングの少し難しい話になります!!

「サブフォルダ含むすべてのフォルダの Excel を文字検索するマクロ」の作り方です。

今回は Excel VBA をつかって処理を実現しています。

処理の中身はこんな感じになっています。

 

この記事でわかること!
  1. 定数と変数を定義
  2. 出力先シート「search」を初期化
  3. フォルダダイアログを表示してフォルダパスを取得
  4. 取得したフォルダパスをもとに Excel ファイル一覧を取得
  5. 取得した Excel ファイルを開き、Cells.Find をつかって文字を検索
  6. 検索結果をシートに出力

 

多いように見えますが、これを処理に起こせば「サブフォルダ含むすべてのフォルダの Excel を文字検索するマクロ」は実現できます。

 

定数と変数を定義

まずはこれらを VBA 処理上でよく使う定数と変数を定義します。

 

  1. SEARCH_WORD・・・検索するファイルの拡張子「\*.xls*」
  2. SHEET_OUTPUT・・・出力先のシート名「search」
  3. CELL_PRINT_COL・・・出力するシートの初期位置(列)、つまり1列目
  4. CELL_PRINT_ROW・・・出力するシートの初期位置(行)、つまり6行目
  5. CELL_SEARCH_WORD・・・検索ワードがあるセル、つまりB3セルを格納
  6. nowRow・・・変数、現在出力している行を格納

 

出力先シート「search」の初期化

つぎに出力先シート「search」を初期化する処理です。

これは出力する初期の列、行(つまり A6セル)から Excel の最終セルの範囲で値を削除します。

 

 

フォルダダイアログを表示してフォルダパスを取得

つぎにフォルダダイアログを表示させてフォルダパスを取得します。

フォルダダイアログっていうのはこれです↓

 

 

Application.FileDialog(msoFileDialogFolderPicker)を用いることで

フォルダダイアログで選択しその結果がフォルダパスが取得できます。

またキャンセルが押下された場合、そのまま処理が終了するように条件分岐しています。

 

 

取得したフォルダパスをもとに Excel ファイル一覧を取得

前章「フォルダダイアログを表示してフォルダパスを取得」で取得したフォルダパスしたパスをもとに

Excel ファイル一覧を取得します。

 

 

フォルダパスの値が Path という変数に格納されていますが、これを Dir 関数に渡すことで

フォルダのフルパスが取得できます。

 

myBookとは今回作成するマクロ自体の情報を格納しています。

これをすることで検索結果出力先のシートをわかりやすくしています。

(検索結果を書き込む際に myBook を呼び出せば、間違えずに書き込むことができます)。

 

またサブフォルダを含めて計算するために再帰処理にしています。

つまり 上記の searchFile() が処理が完了した後にそのフォルダ内にサブフォルダがあれば、

もう一回 searchFile() を呼び出しています。

これをすることでフォルダ内にサブフォルダがなくなるまでずーっと処理が続くようになっています。

 

取得した Excel ファイルを開き、Cells.Find をつかって文字を検索

ここでは 前章「取得したフォルダパスをもとに Excel ファイル一覧を取得」で取得した

Excel ファイルをopen で開き、その開いたファイルのシート一つ一つに対して

Cells.Find をつかって文字検索を実施します。

 

この際に、文字検索結果が複数ある場合も考慮し、

Cells.FindNextをつかって次の検索文字がなくなるまでループ処理をします。

 

 

パスワードつきExcel ファイルの無視する

 

ここでの処理で「If Err.Number = 1004 Then ~ 」というあまり見慣れないものがあると思います。

これはパスワードつきの Excel を開くときのエラーを無視するようにしています。

 

エラー1004コードを受け取った場合、Err.Clearとすることでエラーコードを削除します。

 

検索結果をシートに出力

最後に検索した結果が存在する場合、出力先のシート「search」に出力します。

このとき検索結果のセル位置が存在しない場合、出力しないように条件式を追加しています。

 

 

「サブフォルダ含むすべてのフォルダの Excel を文字検索するマクロ」の全ソース

最後にソースコード全部を紹介します。

いままで長々と説明していましたが、これを貼り付ければ動きます。

また前章までの説明では「searchMacro()」がないと思いますが、これが今回のメイン処理です。

 

 

最後に

 

いかがでしたでしょうか?

この記事では、サブフォルダ含むすべてのフォルダの Excel を文字検索するマクロについて解説しました。

 

これで大量のフォルダの中から膨大な Excel ブックを検索することは日々はなくなると思います。

この記事が読者の何かのお役に立てれば幸いです。

もしこの記事が良いと思ったら SNS とかでシェアしてもらえると嬉しいです。

ではでは。

スポンサーリンク
おすすめの記事