複数ファイルシート一括検索
Windowsにはファイルの内容を検索する機能があります。
しかしExcelを検索する場合、どのファイルにあるかは表示されますが、どのシートにあるかは表示されません。
シートが多いと、そのファイルを開いてから、また検索する必要があります。
そこで、ある文字列について、特定のフォルダにある、すべてのExcelファイルのすべてのシートを検索し、どのシートにあるかを表示するマクロを考えました。
検索結果を表示するだけでなく、該当のセルにハイパーリンクを張るようになっています。
Excel2007で確認しました。
- 適当なExcelファイルを用意します。
- シートの名前を「検索結果」とします。ここに検索結果が表示されます。
- 下に書かれているコード(3つの部分)を標準モジュールに貼り付けます(3つまとめて)。
- マクロ「複数ファイルシート一括検索」を実行します。
- 検索したい文字列を入力します。
- 検索したいフォルダを指定します。
- 検索結果(ファイル名、シート名、セルの内容、リンク)が表示されます。
- 「$A$1」という表示はセルを表し、リンクが張られています。クリックすると該当のファイルを開きジャンプします。
2015年5月22日追記
下階層(フォルダ内のフォルダ)に対応した版を作りました。「複数ファイルシート一括検索(下階層対応版)」
宣言
Dim gyo As Long
モジュールの先頭に書きます。
メイン
Dim myfolder, myfn, myword
myword = InputBox("検索文字列を入力")
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "フォルダを選択"
.AllowMultiSelect = False
If .Show = -1 Then
myfolder = .SelectedItems(1)
Else
Exit Sub
End If
End With
myfn = Dir(myfolder & "\*.xls*", vbNormal)
Sheets(resultsn).Select
Cells.ClearContents
Cells(1, 1) = "ファイル名"
Cells(1, 2) = "シート名"
Cells(1, 3) = "セルの内容"
Cells(1, 4) = "リンク"
gyo = 2
Do Until myfn = ""
Call ファイルを検索(myfolder, myfn, myword)
myfn = Dir
Loop
Sheets(resultsn).Select
End Sub
フォルダの中のファイルを取得して、それぞれ処理します。
検索・編集
Dim mysheet
Dim basefn, mysn, myaddress
Dim kekka
Dim firstaddress
basefn = ThisWorkbook.Name
Workbooks.Open Filename:=myfn
For Each mysheet In Worksheets
mysn = mysheet.Name
Set kekka = mysheet.Cells.Find(myword)
If Not kekka Is Nothing Then
firstaddress = kekka.Address
Do
myaddress = myfolder & "\" & myfn & "#" & mysn & "!" & kekka.Address
With Workbooks(basefn).Sheets(resultsn)
.Cells(gyo, 1) = myfn
.Cells(gyo, 2) = mysn
.Cells(gyo, 3) = kekka.Value
.Hyperlinks.Add Anchor:=.Range("D" & gyo), _
Address:=myaddress, _
TextToDisplay:=kekka.Address
End With
gyo = gyo + 1
Set kekka = mysheet.Cells.FindNext(kekka)
Loop While Not kekka Is Nothing And kekka.Address <> firstaddress
End If
Next
Workbooks(myfn).Close
End Function
取得したファイルに対して検索を実行し見付かった場合にはシートに書き出します。
シートを検索する手法とハイパーリンクを張る手法が使われています。
[ 2011年12月22日 | カテゴリー: Excel | タグ: VBA , シート , 検索 ]
« ワンピースARカードダス一覧 第3弾 | JavaScriptでゼロ埋めする関数 »
コメント
-
マクロ初心者です。
スクリプト実行してみました。すごいですね。
リンクのセルをなくす場合は、下記をコメントか削除すればよいのでしょうか。ご教授ください。
‘Cells(1, 4) = “リンク”
.’Hyperlinks.Add Anchor:=.Range(“D” & gyo), Address:=myadress, TextToDisplay:=kekka.Address -
stabuckyさん、ご回答ありがとうございました。
感謝です! -
まくろ初心者です。
Excel2010で実行してみました。残念ながら、エラーが発生して、インデックスが有効範囲にありませんと出ます。
myfn = Dir(myfolder & “\*.xls”, vbNormal)
Sheets(resultsn).Selectここで止まっています。
何か、改善できる方法がればよろしくお願いします。 -
stabuckyさん、
ご確認、ありがとうございます。
Pathに合わせて正確に指定しなかったことが原因でした。
実行してみたら、本当に使いやすいです~^^ -
素晴らしいですね
マクロって難しい・・・
このマクロで複数ファイルのすべてのセルを検索できるんですが
各シートのB列(名前を入れます)だけを検索するようには
できるのでしょうか是非とも教えてください。
-
やったーーーーー
出来ました。完璧です
非常に嬉しいです(^^)
ご教示のとおり、返還しましたらB列のみをきちんと検索してくれました
まさに天才ですありがとうございました。
感謝の言葉だらけです -
初めまして初心者です宜しくお願いします。
「実行時エラー(1004)ファイルが読めません」
が出て下記の所で停止してしまいます。
Workbooks.Open Filename:=myfn宜しくお願いします
-
迅速な対応有難う御座います。
説明が少なくてすみません検索しようとしているExcelファイルは「.xls」です。
複数のファイルが存在します。「myfn = Dir(myfolder & “\*.xlsx”, vbNormal)」
だと動作しません。「myfn = Dir(myfolder & “\*.xls*”, vbNormal)」
の場合は、「Ifブロックに対応するEnd Ifがありません。」と出てしまいます。宜しくお願いします。
-
\がパソコンで打てず、¥のままではマクロはちゃんとうごきませんよね?
-
大変参考にさせて頂いてます
本件検索の途中で、都度「**の変更内容を保存しますか?」と聞かれ、都度保存しないのボタンをクリックしているのですが、これを自動的に閉じる方法を教えてください
基本的な質問ですいません -
21.の質問は分かりました。お騒がせしてすいませんでした。
-
[…] 以前、「複数ファイルシート一括検索」という記事を書きました。複数のExcelファイルの全てのシートを対象として文字列検索をするマクロです。 フォルダ内の全てのファイルを対象としますが下の階層のフォルダには対応していませんでした。 今回、下の階層を含めて検索するように改良しました。 […]
-
はじめて
初心者です!コードをコピーして貼りつけたのですが、
実行時エラー1004 申し訳ございません。【ファイル名】が見つかりません。名前が変更されたか、移動や削除が行われた可能性があります。と表示されます。
デバッグをクリックするとWorkbooks.Open Filename:=myfnが、黄色くなります。えるささんと同じエラーかと。
Excelは6ファイルあり、全て拡張子は.xls。
一つずつやってもエラーになります。
困りました。。 -
stabuckyさん、ご回答ありがとうございます。
体調不良とご多用中にもかかわらず、調べ方をご教示してくださり感謝です。ウォッチ式、確認してみます。お身体お大事にしてください。
-
お世話になります。
いまとても必要でしたので大変助かります。ありがとうございます!!
ご教示願いたいのですが、フォルダはあらかじめ指定した状態で動かしたいと思いまして、
“D:\各ファイル管理\PAP\実績\SKY” を固定の場合、
どの部分をどう書き換えたらよろしいのでしょうか。
恐れ入りますがよろしくお願いいたします。 -
stabuckyさんありがとうございます。大変勉強になります!!
楽しいブログですので、これからも立ち寄らせていただきます(^^) -
stabuckyさん
こんにちは。マクロ初心者のUmecoと申します。
こちらを初めて拝見しまして早速、利用をさせて頂きました。
複数あるexcelfileのシート検索文字を例えば「赤」(赤い、赤坂、白赤・・など赤が使われているシート名)
検索フォルダはdesktopに新しいフォルダを用意しました(この中に検索対象となる複数のexcelがあります。)
実行してみたところ、私もつきこさんと同様のエラー(2021年11月21日 18:20)となってしまいました。(´;ω;`)
そこでウォッチ式で変数を見てみたところ、ファイル名は空の状態の様でした。
もしかしてexcelファイル名に使用してはいけない文字等はありますか?
例えば、①、②とか_(アンダーバー)など・・・ -
stabuckyさん
早速のお返事大変、助かりました。
エラー消えました(*^▽^*)。本当にこの様なマクロを作って下さり、神です。
ありがとうございます。
マクロ初心者です。
一つ筆問なんですが、下層フォルダーまでは検索できない仕様なのでしょうか?