フォルダ内にあるExcelファイルについてすべてのシート名を取得して一覧にするマクロです。
実行すると対象となるフォルダの問い合わせがあり、そのフォルダにあるExcelファイルをすべてピックアップします。それらを開いてすべてのシート名を取得し閉じます。結果はアクティブになっているシートに貼り付けますので白紙のシートを開いてから実行してください。
なお、そのフォルダの下の階層のファイルについては対応していません。Excel2007で確認しました。
Sub シート名一覧の作成()
Dim fn(1000)
Dim sn(10000, 10)
'フォルダの選択
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "フォルダを選択"
.AllowMultiSelect = False
If .Show = -1 Then
mypath = .SelectedItems(1) & ""
Else
Exit Sub
End If
End With
'ファイル名の取得
fn(1) = Dir(mypath, vbDirectory)
i = 1
Do
i = i + 1
fn(i) = Dir
Loop Until fn(i) = ""
'シート名の取得
x = 0
For j = 1 To i - 1
ext = Mid(fn(j), InStrRev(fn(j), ".") + 1, 3)
If ext = "xls" Then
Workbooks.Open Filename:=fn(j)
For k = 1 To Sheets.Count
sn(x, 1) = fn(j)
sn(x, 2) = Sheets(k).Name
x = x + 1
Next k
ActiveWorkbook.Close
End If
Next j
'シート名一覧の作成
Cells.ClearContents
Cells(1, 1) = mypath
Cells(3, 1) = "ファイル名"
Cells(3, 2) = "シート名"
x = 0
Do
Cells(x + 4, 1) = sn(x, 1)
Cells(x + 4, 2) = sn(x, 2)
x = x + 1
Loop Until sn(x, 1) = ""
End Sub
Dim fn(1000)
Dim sn(10000, 10)
'フォルダの選択
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "フォルダを選択"
.AllowMultiSelect = False
If .Show = -1 Then
mypath = .SelectedItems(1) & ""
Else
Exit Sub
End If
End With
'ファイル名の取得
fn(1) = Dir(mypath, vbDirectory)
i = 1
Do
i = i + 1
fn(i) = Dir
Loop Until fn(i) = ""
'シート名の取得
x = 0
For j = 1 To i - 1
ext = Mid(fn(j), InStrRev(fn(j), ".") + 1, 3)
If ext = "xls" Then
Workbooks.Open Filename:=fn(j)
For k = 1 To Sheets.Count
sn(x, 1) = fn(j)
sn(x, 2) = Sheets(k).Name
x = x + 1
Next k
ActiveWorkbook.Close
End If
Next j
'シート名一覧の作成
Cells.ClearContents
Cells(1, 1) = mypath
Cells(3, 1) = "ファイル名"
Cells(3, 2) = "シート名"
x = 0
Do
Cells(x + 4, 1) = sn(x, 1)
Cells(x + 4, 2) = sn(x, 2)
x = x + 1
Loop Until sn(x, 1) = ""
End Sub
コメント
すいません 教えて下さい 一つのフォルダの中に12くらいのブックがあり、その12位のブックの中に30枚位ずつシートがあります。例えばそのフォルダの中で エクセルの標準についている 検索 を「カーテン」という言葉でかけても ブック名までは検索結果に出て来ますが シート名までは出てこないので、結局は検索結果に出てきた ブック名を開いて そのブックの中で 又検索をかけて どのシートなのかを探して…と一苦労なので、 大きなフォルダの中にある 360くらいあるシートの 全一覧表があって、そしてその中のシート名で 「カーテン」と付いている名前を 検索して出てきた そのシート名をポンと押したら、そのシートにパッと飛べる…なんて事は できるものは ないのでしょうか?すみません 教えていただけないでしょうかm(__)m
返事が遅くなり申し訳ありません。
できるかどうか分かりません。
ちょっと考えさせてください。
@ゆきこ
作ってみました。
記事に書きましたので試してみてください。
http://stabucky.com/wp/archives/3606
はじめまして。Sakuraと申します。
stabucky様のサイトでいつも勉強させて頂いております。
今回は教えて頂きたくてコメントさせて頂きました。
このページのマクロでファイル名・シート名一覧を作成したのですが
その上で更に開いたブックの全シートの特定セル(C3・F3・M16)を抽出し
シート名の隣に同じようにリスト化することは可能でしょうか?
大変お忙しいとは思いますがご指導頂けるとありがたく存じます。
次の通り、3行ずつ挿入すれば一応、動くと思います。
セルC3、F3、M16が「Cells(3,3)」「Cells(3,6)」「Cells(16,13)」になります。
もしセルが変わったらここを直してください。
・「シート名の取得」のところで次の通り3行を挿入。
sn(x, 1) = fn(j)
sn(x, 2) = Sheets(k).Name
sn(x, 3) = Sheets(k).Cells(3, 3) ‘挿入
sn(x, 4) = Sheets(k).Cells(3, 6) ‘挿入
sn(x, 5) = Sheets(k).Cells(16, 13) ‘挿入
・「シート名一覧の作成」のところで次の通り3行を挿入。
Cells(x + 4, 1) = sn(x, 1)
Cells(x + 4, 2) = sn(x, 2)
Cells(x + 4, 3) = sn(x, 3) ‘挿入
Cells(x + 4, 4) = sn(x, 4) ‘挿入
Cells(x + 4, 5) = sn(x, 5) ‘挿入
お世話になります。
早速の回答本当にありがとうございます。
今、試してみたのですがありがとうございます!!
したかった処理が出来ました。
本当に感謝してもしきれません。
ずっとこの処理で悩んでいて毎日100個ずつファイルをいちいち開いていたので本当にありがたいです。
ありがとうございます。
毎日100個! 日報か何かでしょうか。お役に立てて嬉しいです。
また何かあればコメントください。
stabucky様いつも勉強させていただいています。本当に勉強になります。
上記例を参照し、複数のファイルのシート一覧を取得していますが、そのシート内の中のどこかのセルにある特定の文字があったら、その文字以降の文字を取得しシート名の横に記述したいのですが、可能でしょうか。
お忙しいところ申し訳ありませんが、ご教授のほどよろしくお願い致します。
ファイル名 シート名 取得テキストAbで始まる文字
aaa.xls sheetname ab****
別の記事で、複数のファイル、シートを検索して、その文字列を含むセルの内容を書き出すマクロがあります。
http://stabucky.com/wp/archives/3606
参考になれば幸いです。
お忙しいところ回答ありがとうございました。テキストが取得できました。ただ、リンクが1シート目だけは正常に取得できましたが、2シート目からがうまくいきません。すべて1シート目で「参照先が正しくありません」と表示されてしまいます。取得した文字にリンクを設定する方法はありますでしょうか。本当にすみません。私が2007ではなく2003をしようしていることが問題でしょうか。
マクロ自体は最後まで動きますか。
であれば、ハイパーリンクの設定方法がバージョンによって違うのかもしれませんね。
D列は無視していただく、もしくは「.Hyperlinks.Add~kekka.Address」の部分を削除してリンク先を設定しない、という対応になります。申し訳ありません。
stabucky 様、回答ありがとうございます。
マクロ自体は、動作します。テキスト取得できて、本当にたすかりました。古いバージョンでの質問もうしわけありませんでした。そろそろバージョンupします。
また何かありましたら、よろしくお願いします。
はじめまして。itoと申します。
Excel業務で困っていたところ
stabucky様のこのサイトにたどり着きました。
ファイル名・シート名一覧の隣に
ページ数を返すことは可能でしょうか?
Excel初心者で構文の作り方もわかりません。
今回は教えて頂きたくてコメントさせて頂きました。
よろしくお願い致します。
>itoさん
印刷したときの総ページ数ですね。
ちょっと調べてみましたが総ページ数そのものを取得するのが難しいようです。
申し訳ありません。
stabucky様
調べて頂きありがとうございます。
『複数ファイルのシート名一覧』で
格段に作業が進みました。
これからもこのサイトを
参考にさせて頂こうと思います。
ページ数取得はマンパワーで頑張ります!
ありがとうございました。
>itoさん
ページ数取得の件ですが、一応、マクロに反映してみました。次の記事を確認ください。
http://stabucky.com/wp/archives/6446
記事にも書きましたが、正確性が保証されていません。
目安にはなると思いますので、試していただけると幸いです。
stabucky様
回答ありがとうございます。
シート数が3000以上もあり
一覧表を作るのに悩んでいたんですが
無事ページ数取得出来ました。
総ページ数もあるんですね。
すごく困っていたので本当に助かりました。
ありがとうございます!
>itoさん
3000シート!
それはマクロを使わないと大変ですね。
お役に立てて嬉しいです。