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

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

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

Range("A3:B10000").Clear

VBAを実装する

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

VBAを実行する

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

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

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

 

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
        

        '別ブック一覧があるシートの3行目からパスを取得します。
        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

VBAの設定をする

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

別ブック一覧があるシート名を指定します。

strBookPassList = "別ブックパス一覧"

出力先のシート名を指定します。

strSheetNameList = "別ブックシート名一覧表"

VBAを実行する

VBAを実行してみましょう。

はい!「別ブックシート名一覧表」シートに各ブックのシート名とパスが出力されましたね!

シート名の内容も問題ないですね!

VBAの説明

VBAの内容について説明をします。

B列を対象に最終行数を取得します。

intLastRowNum = .Sheets(strBookPassList).Cells(Rows.Count, 2).End(xlUp).Row

出力先のシートA列を対象に最終行数を取得します。

intLastRowNum2 = .Sheets(strSheetNameList).Cells(Rows.Count, 1).End(xlUp).Row

別ブック一覧があるシートの3行目からパスを取得します。

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 Next

出力先シートのA列へ対象ブックのパスを出力します。

.Sheets(strSheetNameList).Cells(intLastRowNum2, 1).Value = strBookList

出力先シートのA列へ対象ブックのシート名を出力します。

.Sheets(strSheetNameList).Cells(intLastRowNum2, 2).Value = objSheetName.Name

範囲選択を解除します。

Application.CutCopyMode = False

オブジェクトを解放します。

Set objWorkbook = Nothing

タカヒロ
タカヒロ
シート名を取得する方法についてはこちらに詳しく記載されていますので、よろしければこちらもご参考ください。

Excelのシート名を取得する方法!関数の他VBAで一括取得も!

VBAの実装手順

実装手順は以下の通りです。

Excel側にVBAを実装していきます。

①Excelを新規に開き、「開発」タブをクリックし、「VisualBasic」をクリックします。
もしくはショートカットキー「Alt」+「F11」でもOKです。

②標準モジュールを追加します。
左ペインのVBAProjectを右クリックし、「挿入」、「標準モジュール」を選択します。

③右ペインのウインドウに上記のVBAを入力します。

こちらで完了です。

VBAを実行する

では早速VBAの実行をしてみましょう。

①「開発」タブの「VBA」をクリックし実行したいマクロを選択し、「実行」をクリックします。

②処理がされたことが確認できれば完了です。

さいごに

いかがでしょうか。

今回は、

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

についてまとめました。

また、他にも便利な方法がありますので、よろしければご参照頂ければと思います。



この記事の関連キーワード

こちらの記事の関連キーワード一覧です。クリックするとキーワードに関連する記事一覧が閲覧できます。









コメントを残す

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