Excelの複数ファイルのシート名一覧を作成

Pocket

フォルダ内にある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

[ 2011年2月4日 | カテゴリー: Excel | タグ: , ]

« | »

コメント

  1. ゆきこ より:

    すいません 教えて下さい 一つのフォルダの中に12くらいのブックがあり、その12位のブックの中に30枚位ずつシートがあります。例えばそのフォルダの中で エクセルの標準についている 検索 を「カーテン」という言葉でかけても ブック名までは検索結果に出て来ますが シート名までは出てこないので、結局は検索結果に出てきた ブック名を開いて そのブックの中で 又検索をかけて どのシートなのかを探して…と一苦労なので、 大きなフォルダの中にある 360くらいあるシートの 全一覧表があって、そしてその中のシート名で 「カーテン」と付いている名前を 検索して出てきた そのシート名をポンと押したら、そのシートにパッと飛べる…なんて事は できるものは ないのでしょうか?すみません 教えていただけないでしょうかm(__)m

  2. stabucky より:

    返事が遅くなり申し訳ありません。
    できるかどうか分かりません。
    ちょっと考えさせてください。

  3. stabucky より:

    @ゆきこ
    作ってみました。
    記事に書きましたので試してみてください。
    http://stabucky.com/wp/archives/3606

  4. Sakura より:

    はじめまして。Sakuraと申します。
    stabucky様のサイトでいつも勉強させて頂いております。
    今回は教えて頂きたくてコメントさせて頂きました。

    このページのマクロでファイル名・シート名一覧を作成したのですが
    その上で更に開いたブックの全シートの特定セル(C3・F3・M16)を抽出し
    シート名の隣に同じようにリスト化することは可能でしょうか?

    大変お忙しいとは思いますがご指導頂けるとありがたく存じます。

  5. stabucky より:

    次の通り、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) ‘挿入

  6. Sakura より:

    お世話になります。
    早速の回答本当にありがとうございます。
    今、試してみたのですがありがとうございます!!
    したかった処理が出来ました。
    本当に感謝してもしきれません。
    ずっとこの処理で悩んでいて毎日100個ずつファイルをいちいち開いていたので本当にありがたいです。

    ありがとうございます。

  7. stabucky より:

    毎日100個! 日報か何かでしょうか。お役に立てて嬉しいです。
    また何かあればコメントください。

  8. kouki より:

    stabucky様いつも勉強させていただいています。本当に勉強になります。
    上記例を参照し、複数のファイルのシート一覧を取得していますが、そのシート内の中のどこかのセルにある特定の文字があったら、その文字以降の文字を取得しシート名の横に記述したいのですが、可能でしょうか。
    お忙しいところ申し訳ありませんが、ご教授のほどよろしくお願い致します。

    ファイル名  シート名  取得テキストAbで始まる文字
    aaa.xls sheetname       ab****

  9. stabucky より:

    別の記事で、複数のファイル、シートを検索して、その文字列を含むセルの内容を書き出すマクロがあります。
    http://stabucky.com/wp/archives/3606
    参考になれば幸いです。

  10. kouki より:

    お忙しいところ回答ありがとうございました。テキストが取得できました。ただ、リンクが1シート目だけは正常に取得できましたが、2シート目からがうまくいきません。すべて1シート目で「参照先が正しくありません」と表示されてしまいます。取得した文字にリンクを設定する方法はありますでしょうか。本当にすみません。私が2007ではなく2003をしようしていることが問題でしょうか。

  11. stabucky より:

    マクロ自体は最後まで動きますか。
    であれば、ハイパーリンクの設定方法がバージョンによって違うのかもしれませんね。
    D列は無視していただく、もしくは「.Hyperlinks.Add~kekka.Address」の部分を削除してリンク先を設定しない、という対応になります。申し訳ありません。

  12. kouki より:

    stabucky 様、回答ありがとうございます。
    マクロ自体は、動作します。テキスト取得できて、本当にたすかりました。古いバージョンでの質問もうしわけありませんでした。そろそろバージョンupします。

  13. stabucky より:

    また何かありましたら、よろしくお願いします。

  14. ito より:

    はじめまして。itoと申します。
    Excel業務で困っていたところ
    stabucky様のこのサイトにたどり着きました。
    ファイル名・シート名一覧の隣に
    ページ数を返すことは可能でしょうか?
    Excel初心者で構文の作り方もわかりません。
    今回は教えて頂きたくてコメントさせて頂きました。
    よろしくお願い致します。

  15. stabucky より:

    >itoさん
    印刷したときの総ページ数ですね。
    ちょっと調べてみましたが総ページ数そのものを取得するのが難しいようです。
    申し訳ありません。

  16. ito より:

    stabucky様
    調べて頂きありがとうございます。
    『複数ファイルのシート名一覧』で
    格段に作業が進みました。
    これからもこのサイトを
    参考にさせて頂こうと思います。
    ページ数取得はマンパワーで頑張ります!
    ありがとうございました。

  17. stabucky より:

    >itoさん
    ページ数取得の件ですが、一応、マクロに反映してみました。次の記事を確認ください。
    http://stabucky.com/wp/archives/6446
    記事にも書きましたが、正確性が保証されていません。
    目安にはなると思いますので、試していただけると幸いです。

  18. ito より:

    stabucky様
    回答ありがとうございます。
    シート数が3000以上もあり
    一覧表を作るのに悩んでいたんですが
    無事ページ数取得出来ました。
    総ページ数もあるんですね。
    すごく困っていたので本当に助かりました。

    ありがとうございます!

  19. stabucky より:

    >itoさん
    3000シート!
    それはマクロを使わないと大変ですね。
    お役に立てて嬉しいです。

コメントを残す

メールアドレスが公開されることはありません。 が付いている欄は必須項目です

送信してください。


タグ

カテゴリー

最近の投稿

最近のコメント

固定ページ

アーカイブ

stabucky

写真

メタ情報