【EXCEL：VBA】フォルダに存在するEXCELの全シート名を取得したい

この記事では、指定されたフォルダに存在するEXCELファイルの全シート名を取得し、一覧化する方法について説明しています。

VBAを利用すれば、フォルダに存在するファイルを全て取得でき、１つ１つEXCELファイルを手で開く必要はありません。

しかも、全てのシート名を間違いなく取得し、簡単に一覧化することができます。

それでは、指定したフォルダ配下の全てのEXCELファイルを対象に、全てのシート名を調べる機能を作成していきましょう。

困っている女性

大量に作成されているEXCELファイルに存在するシート名を調べるが地味に大変なの・・・、わかる！？

困っている女性

仕事でたくさんEXCELファイルを作成するので、あのシートはどのEXCELファイルに存在するか、探すことがよくあるの。

困っている女性

そういう時に、１つ１つEXCELファイルを開いて目的のシートを探すのが大変なの・・・。

それなら、VBAを使えばボタン1つ押すだけで、大量のEXCELファイルから目的のシートをみつけることができるよ。

解決する男性

EXCELファイルが存在するフォルダパスを指定すれば、そのフォルダに存在するEXCELファイルの全シートを一気に調べれるよ。

解決する男性

困っている女性

それそれ、早く教えてよ。

◆似たような作業で、ファイル一覧を取得する方法については、以下の記事を参照してください。

2020-06-13【EXCEL：VBA】指定したフォルダに存在するファイル一覧を取得したい

◆他には、指定したフォルダ階層を一気に作成する方法については、以下の記事を参照してください。

2020-06-15【EXCEL：VBA】一括で大量にフォルダを作成したい

複数EXCELファイルの全シート名を一気に調べる機能を、1つ1つ順番に説明していきます。

フォルダ指定欄、シート名リスト表示欄を準備する

まず、フォルダを指定する欄とそのフォルダに存在するEXCELファイル名とシート名を表示させる欄を作成します。

「メイン」シートを作成する

メインシートを作成する

「メイン」という名前のシートを作成します。
1行目に「フォルダパス」のタイトル行を作成します。
2行目に「フォルダパス」を入力する欄を作成します。
3行目に「ファイル一覧」のタイトル行を作成します。
4行目に「ファイル名」、「シート名」のタイトル行を作成します。
5行目以降は、指定されたフォルダに存在するEXCELファイル名とシート名を書き込む欄になります。

5行目以降の「ファイル名」、「シート名」はVBAプログラムが記入してくれます。

VBAでシート名を取得する機能を記入する

開発タブの「Visual Basic」をクリックして、「Microsoft Visual Basic for Applications」を起動します。

Microsoft Visual Basic for Applicationsを起動する

「Microsoft Visual Basic for Applications」にVBAプログラムを入力します。

VBAプログラムを入力する（その１）

VBAプログラムを入力する（その2）

標準モジュールを追加して、以下のソースを書きます。

Public Sub Main_Proc()
    Dim MaxRow As Long
    Dim folderPath As String
    Dim shtMain As Worksheet
    Dim fso As Object
    Dim f As Object
    Dim nowRow As Long
    Dim wb As Workbook
    Dim i As Integer
    
    '①参照するシートを変数に格納する
    Set shtMain = ThisWorkbook.Sheets("メイン")
    
    '②データ入力されているA列の最終行を取得する
    MaxRow = shtMain.Cells(shtMain.Rows.Count, 1).End(xlUp).Row
    
    '③最終行が5行目以降なら、5行目以降のA列とB列のセルをクリアする
    If MaxRow >= 5 Then
        shtMain.Range("A5:B" & MaxRow).Clear
    End If
    
    '④FileSystemObjectを変数に格納する
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    nowRow = 4
    
    '⑤指定されているフォルダに存在するファイル数分処理を繰り返す
    For Each f In fso.GetFolder(shtMain.Range("A2")).Files
    
        '⑥拡張子がxlsxのファイルのみ対象とする
        If LCase(fso.getextensionName(f.Name)) = "xlsx" Then
        
            '⑦EXCELファイルを開く
            Set wb = Workbooks.Open(shtMain.Range("A2") & "\" & f.Name)
            
            '⑧存在するシート数分繰り返す
            For i = 1 To wb.Sheets.Count
                '⑨現在行を次の行に変更する
                nowRow = nowRow + 1
        
                '⑩ファイル名をA列にセットする
                shtMain.Range("A" & nowRow) = f.Name
                
                '⑪シート名をB列にセットする
                shtMain.Range("B" & nowRow) = wb.Sheets(i).Name
                
            Next
            
            '⑫EXCELファイルを閉じる
            wb.Close
            
        End If
        
    Next
    
    '⑬ワークブックをクリアする
    Set wb = Nothing
    
    '⑭FileSystemObjectをクリアする
    Set fso = Nothing
    
    MsgBox "完了"
    
End Sub

Public Sub Main_Proc()

Dim MaxRow As Long

Dim folderPath As String

Dim shtMain As Worksheet

Dim fso As Object

Dim f As Object

Dim nowRow As Long

Dim wb As Workbook

Dim i As Integer

'①参照するシートを変数に格納する

Set shtMain = ThisWorkbook.Sheets("メイン")

'②データ入力されているA列の最終行を取得する

MaxRow = shtMain.Cells(shtMain.Rows.Count, 1).End(xlUp).Row

'③最終行が5行目以降なら、5行目以降のA列とB列のセルをクリアする

If MaxRow >= 5 Then

shtMain.Range("A5:B" & MaxRow).Clear

End If

'④FileSystemObjectを変数に格納する

Set fso = CreateObject("Scripting.FileSystemObject")

nowRow = 4

'⑤指定されているフォルダに存在するファイル数分処理を繰り返す

For Each f In fso.GetFolder(shtMain.Range("A2")).Files

'⑥拡張子がxlsxのファイルのみ対象とする

If LCase(fso.getextensionName(f.Name)) = "xlsx" Then

'⑦EXCELファイルを開く

Set wb = Workbooks.Open(shtMain.Range("A2") & "\" & f.Name)

'⑧存在するシート数分繰り返す

For i = 1 To wb.Sheets.Count

'⑨現在行を次の行に変更する

nowRow = nowRow + 1

'⑩ファイル名をA列にセットする

shtMain.Range("A" & nowRow) = f.Name

'⑪シート名をB列にセットする

shtMain.Range("B" & nowRow) = wb.Sheets(i).Name

'⑫EXCELファイルを閉じる

wb.Close

End If

'⑬ワークブックをクリアする

Set wb = Nothing

'⑭FileSystemObjectをクリアする

Set fso = Nothing

MsgBox "完了"

End Sub

最後にEXCELを「◯◯◯.xlsm」というような感じでマクロ有効ブック形式で保存します。

EXCELファイルの全シート名を調べる機能の使い方

それでは、シート名を調べる方法を説明します。

まず、A2セルにEXCELファイルが存在するフォルダパスを入力します。

次に、「開発」タブの「マクロ」をクリックします。

VBAプログラムを実行する

先程作成した自作関数「Main_Proc」が一覧に表示されていますので、選択します。

次に、「実行」ボタンをクリックしてファイル名とシート名を取得します。

実行ボタンをクリックする

しばらく待つと、指定したフォルダ配下に存在する全EXCELファイル名とシート名が5行目以降に書かれます。

全シート名を取得する機能を実行した結果

A列には、EXCELファイル名が書かれます。

B列には、A列のEXCELファイルに存在するシート名が書かれます。

ちなみに、実際のフォルダは以下のようになっています。

実際のフォルダの様子

シート名を調べるVBAプログラム（Main_Proc）の説明

それでは、VBAプログラム（Main_Proc）を説明します。

①参照するシートを変数に格納する

'①参照するシートを変数に格納する
Set shtMain = ThisWorkbook.Sheets("メイン")

1 2	'①参照するシートを変数に格納する Set shtMain = ThisWorkbook.Sheets("メイン")

ここでは、このあとの処理で何度も使う「メイン」シートを変数に格納しています。

そうすることにより、入力するプログラムを短くすることができるからです。

何度も「ThisWorkbook.Sheets(“メイン”)」と入力するより、「shtMain」と入力するほうがコードが少なくてよいからです。

②データ入力されているA列の最終行を取得する

'②データ入力されているA列の最終行を取得する
MaxRow = shtMain.Cells(shtMain.Rows.Count, 1).End(xlUp).Row

1 2	'②データ入力されているA列の最終行を取得する MaxRow = shtMain.Cells(shtMain.Rows.Count, 1).End(xlUp).Row

ここでは、A列の入力されている最終行を変数に格納しています。

何度も実行すると、前回実行時のEXCELファイル名が入力されているので、

処理の実行前にセルに入力されている値をクリアするために、

値が入力されている最終行を取得します。

最終行を取得することで、どこまでのセルをクリアするべきかがわかるからです。

③最終行が5行目以降なら、5行目以降のA列とB列のセルをクリアする

'③最終行が5行目以降なら、5行目以降のA列とB列のセルをクリアする
If MaxRow >= 5 Then
    shtMain.Range("A5:B" & MaxRow).Clear
End If

'③最終行が5行目以降なら、5行目以降のA列とB列のセルをクリアする

If MaxRow >= 5 Then

shtMain.Range("A5:B" & MaxRow).Clear

End If

ここでは、先程取得した最終行が5以上なら、セルの値をクリアする必要があります。

その場合、A5セルからB列の最終行までをクリアします。

この処理によって、すでに入力されているファイル名とシート名をクリアされます。

④FileSystemObjectを変数に格納する

'④FileSystemObjectを変数に格納する
Set fso = CreateObject("Scripting.FileSystemObject")

1 2	'④FileSystemObjectを変数に格納する Set fso = CreateObject("Scripting.FileSystemObject")

ここでは、「FileSystemObject」を変数に格納しています。

「FileSystemObject」は、フォルダに存在するファイルを調べることに利用します。

⑤指定されているフォルダに存在するファイル数分処理を繰り返す

'⑤指定されているフォルダに存在するファイル数分処理を繰り返す
For Each f In fso.GetFolder(shtMain.Range("A2")).Files

1 2	'⑤指定されているフォルダに存在するファイル数分処理を繰り返す For Each f In fso.GetFolder(shtMain.Range("A2")).Files

ここでは、A2セルに入力されたフォルダに存在するファイル数分、以降の処理を繰り返します。

⑥拡張子がxlsxのファイルのみ対象とする

'⑥拡張子がxlsxのファイルのみ対象とする
If LCase(fso.getextensionName(f.Name)) = "xlsx" Then

1 2	'⑥拡張子がxlsxのファイルのみ対象とする If LCase(fso.getextensionName(f.Name)) = "xlsx" Then

ここでは、フォルダに存在するファイルの拡張子が「xlsx」か確認しています。

拡張子が「xlsx」のファイルのみを対象に、以降の処理を行います。

例えば、拡張子が「txt」なら、対象外となります。

⑦EXCELファイルを開く

'⑦EXCELファイルを開く
Set wb = Workbooks.Open(shtMain.Range("A2") & "\" & f.Name)

1 2	'⑦EXCELファイルを開く Set wb = Workbooks.Open(shtMain.Range("A2") & "\" & f.Name)

ここでは、フォルダに存在するEXCELファイルを開いています。

⑧存在するシート数分繰り返す

'⑧存在するシート数分繰り返す
For i = 1 To wb.Sheets.Count

1 2	'⑧存在するシート数分繰り返す For i = 1 To wb.Sheets.Count

ここでは、開いたEXCELファイルに存在するシート数分、以降の処理を繰り返します。

⑨現在行を次の行に変更する

'⑨現在行を次の行に変更する
nowRow = nowRow + 1

1 2	'⑨現在行を次の行に変更する nowRow = nowRow + 1

ここでは、「メイン」シートに書き込みする行を次の行に変更しています。

⑩ファイル名をA列にセットする

'⑩ファイル名をA列にセットする
shtMain.Range("A" & nowRow) = f.Name

1 2	'⑩ファイル名をA列にセットする shtMain.Range("A" & nowRow) = f.Name

ここでは、取得したEXCELファイル名を現在行のA列に記入しています。

⑪シート名をB列にセットする

'⑪シート名をB列にセットする
shtMain.Range("B" & nowRow) = wb.Sheets(i).Name

1 2	'⑪シート名をB列にセットする shtMain.Range("B" & nowRow) = wb.Sheets(i).Name

ここでは、取得したシート名を現在行のB列に記入しています。

⑫EXCELファイルを閉じる

'⑫EXCELファイルを閉じる
wb.Close

1 2	'⑫EXCELファイルを閉じる wb.Close

ここでは、開いたEXCELファイルを閉じています。

全てのシート名を「メイン」シートに記入し終わると、EXCELファイルを閉じます。

⑬ワークブックをクリアする

'⑬ワークブックをクリアする
Set wb = Nothing

1 2	'⑬ワークブックをクリアする Set wb = Nothing

ここでは、ワークブックオブジェクトをクリアしています。

⑭FileSystemObjectをクリアする

'⑭FileSystemObjectをクリアする
Set fso = Nothing

1 2	'⑭FileSystemObjectをクリアする Set fso = Nothing

ここでは、使い終わったFileSystemObjectをクリアしています。

結果として、指定されたフォルダに存在するEXCELファイル全ての全シートを調べることができるようになりました。

それでは、シート名一覧を取得するツールを作って、作業を効率化しましょう。

以上です。

フォルダ指定欄、シート名リスト表示欄を準備する

「メイン」シートを作成する

VBAでシート名を取得する機能を記入する

EXCELファイルの全シート名を調べる機能の使い方

シート名を調べるVBAプログラム（Main_Proc）の説明

①参照するシートを変数に格納する

②データ入力されているA列の最終行を取得する

③最終行が5行目以降なら、5行目以降のA列とB列のセルをクリアする

④FileSystemObjectを変数に格納する

⑤指定されているフォルダに存在するファイル数分処理を繰り返す

⑥拡張子がxlsxのファイルのみ対象とする

⑦EXCELファイルを開く

⑧存在するシート数分繰り返す

⑨現在行を次の行に変更する

⑩ファイル名をA列にセットする

⑪シート名をB列にセットする

⑫EXCELファイルを閉じる

⑬ワークブックをクリアする

⑭FileSystemObjectをクリアする

コメントを残す コメントをキャンセル

コメントを残すコメントをキャンセル