Excel VBAで複数ブックのシート名一覧表を作成する方法！

Excel VBAでシート名一覧表を作成したいときはないでしょうか。

けど、そんな中で悩むことは、

・Excel VBAでシート名一覧表を作成したいが方法がよくわからない
・Excel VBAでフォルダにある複数ブックのシート名を取得し一つの表にまとめたいが方法がよくわからない

ですよね。

今回はそんなお悩みを解決する

・Excel VBAでシート名一覧表を作成する方法
・Excel VBAでフォルダにある複数ブックのシート名を取得し一つの表にまとめる方法

についてまとめます！

Excel VBAでシート名一覧表を作成するイメージ

Excel VBAでシート名一覧表を作成するイメージについて説明をします。

複数のExcelブックに

それぞれ複数のシートを用意します。

次にデータをまとめる先となるExcelブックを用意します。

VBAを実行すると

まず、フォルダにあるブックのリストが収集されます。

次にVBAを実行すると、リストに沿ってブックが開かれ、

データまとめ先のExcelブックのシートへシート名一覧が出力されていきます。

この処理をフォルダにあるExcelブック分繰り返して、完成となります。

データの準備

まずはデータの準備をしましょう。特定シートにデータが入力されている複数のExcelブックを用意し、

フォルダに格納します。

Excel VBAでフォルダにある複数ブックのリストを取得

Excel VBAでフォルダにある複数ブックのリストを取得していきましょう。

まとめ用ブックを用意する

まとめ用ブックを用意します。シート名は「別ブックパス一覧」とします。

また出力先のシートも用意します。シート名は「別ブックシート名一覧表」とします。

VBAコード

VBAコードは以下の通りです。

Sub フォルダ内のファイル名やファイルパスを取得()
    
    Dim intIchi As Integer
    Dim strFolderpass As String
    Dim objfFSO As Object
    Dim objFiles As Object
    Dim objFile As Object
    
    
    'ファイル名一覧を出力する行番号を指定します。
    intIchi = 3
    
    '対象フォルダのファイルパスがあるセルを指定し、値を代入します。
    strFolderpass = Range("B1").Value
    
    '指定範囲の値をクリアします。
    Range("A3:B10000").Clear
    
    
    '対象フォルダのファイルパスの値があれば処理をおこないます。
    If strFolderpass = "" Then
        MsgBox "対象フォルダのファイルパスがありません。ファイルパスを入力してください。"
    Else
    
        'FileSystemObjectはファイルやフォルダを操作する専用のオブジェクトです｡インスタンスにセットします。
        Set objfFSO = CreateObject("Scripting.FileSystemObject")
    
        '対象フォルダのファイルオブジェクトをセットします。
        Set objFiles = objfFSO.GetFolder(strFolderpass).Files
        
        'ファイル情報を出力します。
        For Each objFile In objFiles
            Cells(intIchi, 1) = objFile.Name
            Cells(intIchi, 2) = objFile.Path
            intIchi = intIchi + 1
        Next objFile
    
    End If
    
    Set objfFSO = Nothing
    Set objFiles = Nothing
    Set objFile = Nothing

End Sub

VBAの設定をする

VBAの設定について説明をします。

ファイル名一覧を出力する行番号を指定します。
今回は3行目指定とします。

intIchi = 3

対象フォルダのファイルパスがあるセルを指定し、値を代入します。
B1セル以外にパスを記載したい場合は変更をしてください。

strFolderpass = Range("B1").Value

指定範囲の値をクリアします。
行数が１万件以上ある場合は変更をしてください。１万件以内であればそのままで結構です。

Range("A3:B10000").Clear

VBAを実装する

VBAの実装方法については
VBAの実装手順
をご参照ください。

VBAを実行する

B1セルにブックが格納されているフォルダのパスを記載します。

VBAを実行してみましょう。
「フォルダ内のファイル名やファイルパスを取得」を実行します。

はい！３行目以降にブックのブック名とパスが出力されましたね！

VBAの説明

VBAの内容については以下記事をご参照ください。

【Excel VBA】フォルダ内のフォルダ名を取得する方法！サブフォルダ名も取得可！

Excel VBAでシート名一覧表を作成する

Excel VBAでシート名一覧表を作成していきましょう。

VBAコード

VBAコードは以下の通りです。

Sub 各ブックのシート名一覧をまとめ用ブックへ出力する()

    Dim objWorkbook As Workbook
    Dim objSheetName As Object
    Dim i As Integer
    Dim strBookList As Variant
    Dim intLastRowNum As Integer
    Dim intLastRowNum2 As Integer
    Dim strBookPassList As String
    Dim strSheetNameList As String
    
    '別ブック一覧があるシート名を指定します。
    strBookPassList = "別ブックパス一覧"

    '出力先のシート名を指定します。
    strSheetNameList = "別ブックシート名一覧表"

    With ThisWorkbook
        'B列を対象に最終行数を取得します。
        intLastRowNum = .Sheets(strBookPassList).Cells(Rows.Count, 2).End(xlUp).Row
        
        '出力先のシートA列を対象に最終行数を取得します。
        intLastRowNum2 = .Sheets(strSheetNameList).Cells(Rows.Count, 1).End(xlUp).Row
        

        '別ブック一覧があるシートの３行目からパスを取得します。
        For i = 3 To intLastRowNum
            
            '別ブックパスを取得します。
            strBookList = .Sheets(strBookPassList).Cells(i, 2).Value
            
            '別ブックを開きます。
            Set objWorkbook = Workbooks.Open(strBookList)
            
                
            'Sheetsコレクションから1シートづつオブジェクトを取得します。
            For Each objSheetName In objWorkbook.Sheets
                
                '出力先シートのA列へ対象ブックのパスを出力します。
                 .Sheets(strSheetNameList).Cells(intLastRowNum2, 1).Value = strBookList
                
                '出力先シートのA列へ対象ブックのシート名を出力します。
                .Sheets(strSheetNameList).Cells(intLastRowNum2, 2).Value = objSheetName.Name
            
                intLastRowNum2 = intLastRowNum2 + 1
            Next
                
        Next i
        
    End With


    '範囲選択を解除します。
    Application.CutCopyMode = False
    
    'オブジェクトを解放します。
    Set objWorkbook = Nothing
    
  
End Sub