Excelの複数ファイルのシート名一覧を作成
フォルダ内にあるExcelファイルについてすべてのシート名を取得して一覧にするマクロです。
実行すると対象となるフォルダの問い合わせがあり、そのフォルダにあるExcelファイルをすべてピックアップします。それらを開いてすべてのシート名を取得し閉じます。結果はアクティブになっているシートに貼り付けますので白紙のシートを開いてから実行してください。
なお、そのフォルダの下の階層のファイルについては対応していません。Excel2007で確認しました。
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
[ 2011年2月4日 | カテゴリー: Excel | タグ: VBA , シート ]
« 定期的にパスワードを変える効果は意外に少ない | 国内ジム所属の現役世界王者 »
コメント
-
はじめまして。Sakuraと申します。
stabucky様のサイトでいつも勉強させて頂いております。
今回は教えて頂きたくてコメントさせて頂きました。このページのマクロでファイル名・シート名一覧を作成したのですが
その上で更に開いたブックの全シートの特定セル(C3・F3・M16)を抽出し
シート名の隣に同じようにリスト化することは可能でしょうか?大変お忙しいとは思いますがご指導頂けるとありがたく存じます。
-
お世話になります。
早速の回答本当にありがとうございます。
今、試してみたのですがありがとうございます!!
したかった処理が出来ました。
本当に感謝してもしきれません。
ずっとこの処理で悩んでいて毎日100個ずつファイルをいちいち開いていたので本当にありがたいです。ありがとうございます。
-
stabucky様いつも勉強させていただいています。本当に勉強になります。
上記例を参照し、複数のファイルのシート一覧を取得していますが、そのシート内の中のどこかのセルにある特定の文字があったら、その文字以降の文字を取得しシート名の横に記述したいのですが、可能でしょうか。
お忙しいところ申し訳ありませんが、ご教授のほどよろしくお願い致します。ファイル名 シート名 取得テキストAbで始まる文字
aaa.xls sheetname ab**** -
お忙しいところ回答ありがとうございました。テキストが取得できました。ただ、リンクが1シート目だけは正常に取得できましたが、2シート目からがうまくいきません。すべて1シート目で「参照先が正しくありません」と表示されてしまいます。取得した文字にリンクを設定する方法はありますでしょうか。本当にすみません。私が2007ではなく2003をしようしていることが問題でしょうか。
-
stabucky 様、回答ありがとうございます。
マクロ自体は、動作します。テキスト取得できて、本当にたすかりました。古いバージョンでの質問もうしわけありませんでした。そろそろバージョンupします。 -
はじめまして。itoと申します。
Excel業務で困っていたところ
stabucky様のこのサイトにたどり着きました。
ファイル名・シート名一覧の隣に
ページ数を返すことは可能でしょうか?
Excel初心者で構文の作り方もわかりません。
今回は教えて頂きたくてコメントさせて頂きました。
よろしくお願い致します。 -
stabucky様
調べて頂きありがとうございます。
『複数ファイルのシート名一覧』で
格段に作業が進みました。
これからもこのサイトを
参考にさせて頂こうと思います。
ページ数取得はマンパワーで頑張ります!
ありがとうございました。 -
stabucky様
回答ありがとうございます。
シート数が3000以上もあり
一覧表を作るのに悩んでいたんですが
無事ページ数取得出来ました。
総ページ数もあるんですね。
すごく困っていたので本当に助かりました。ありがとうございます!
すいません 教えて下さい 一つのフォルダの中に12くらいのブックがあり、その12位のブックの中に30枚位ずつシートがあります。例えばそのフォルダの中で エクセルの標準についている 検索 を「カーテン」という言葉でかけても ブック名までは検索結果に出て来ますが シート名までは出てこないので、結局は検索結果に出てきた ブック名を開いて そのブックの中で 又検索をかけて どのシートなのかを探して…と一苦労なので、 大きなフォルダの中にある 360くらいあるシートの 全一覧表があって、そしてその中のシート名で 「カーテン」と付いている名前を 検索して出てきた そのシート名をポンと押したら、そのシートにパッと飛べる…なんて事は できるものは ないのでしょうか?すみません 教えていただけないでしょうかm(__)m