この記事では、指定されたフォルダに存在するEXCELファイルの全シート名を取得し、一覧化する方法について説明しています。
VBAを利用すれば、フォルダに存在するファイルを全て取得でき、１つ１つEXCELファイルを手で開く必要はありません。
しかも、全てのシート名を間違いなく取得し、簡単に一覧化することができます。
それでは、指定したフォルダ配下の全てのEXCELファイルを対象に、全てのシート名を調べる機能を作成していきましょう。
困っている女性
困っている女性
困っている女性
解決する男性
解決する男性
困っている女性
◆似たような作業で、ファイル一覧を取得する方法については、以下の記事を参照してください。
◆他には、指定したフォルダ階層を一気に作成する方法については、以下の記事を参照してください。
複数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
最後に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("メイン")
ここでは、このあとの処理で何度も使う「メイン」シートを変数に格納しています。
そうすることにより、入力するプログラムを短くすることができるからです。
何度も「ThisWorkbook.Sheets(“メイン”)」と入力するより、「shtMain」と入力するほうがコードが少なくてよいからです。
②データ入力されているA列の最終行を取得する
'②データ入力されている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以上なら、セルの値をクリアする必要があります。
その場合、A5セルからB列の最終行までをクリアします。
この処理によって、すでに入力されているファイル名とシート名をクリアされます。
④FileSystemObjectを変数に格納する
'④FileSystemObjectを変数に格納する
Set fso = CreateObject("Scripting.FileSystemObject")
ここでは、「FileSystemObject」を変数に格納しています。
「FileSystemObject」は、フォルダに存在するファイルを調べることに利用します。
⑤指定されているフォルダに存在するファイル数分処理を繰り返す
'⑤指定されているフォルダに存在するファイル数分処理を繰り返す
For Each f In fso.GetFolder(shtMain.Range("A2")).Files
ここでは、A2セルに入力されたフォルダに存在するファイル数分、以降の処理を繰り返します。
⑥拡張子がxlsxのファイルのみ対象とする
'⑥拡張子がxlsxのファイルのみ対象とする If LCase(fso.getextensionName(f.Name)) = "xlsx" Then
ここでは、フォルダに存在するファイルの拡張子が「xlsx」か確認しています。
拡張子が「xlsx」のファイルのみを対象に、以降の処理を行います。
例えば、拡張子が「txt」なら、対象外となります。
⑦EXCELファイルを開く
'⑦EXCELファイルを開く
Set wb = Workbooks.Open(shtMain.Range("A2") & "\" & f.Name)
ここでは、フォルダに存在するEXCELファイルを開いています。
⑧存在するシート数分繰り返す
'⑧存在するシート数分繰り返す For i = 1 To wb.Sheets.Count
ここでは、開いたEXCELファイルに存在するシート数分、以降の処理を繰り返します。
⑨現在行を次の行に変更する
'⑨現在行を次の行に変更する nowRow = nowRow + 1
ここでは、「メイン」シートに書き込みする行を次の行に変更しています。
⑩ファイル名をA列にセットする
'⑩ファイル名をA列にセットする
shtMain.Range("A" & nowRow) = f.Name
ここでは、取得したEXCELファイル名を現在行のA列に記入しています。
⑪シート名をB列にセットする
'⑪シート名をB列にセットする
shtMain.Range("B" & nowRow) = wb.Sheets(i).Name
ここでは、取得したシート名を現在行のB列に記入しています。
⑫EXCELファイルを閉じる
'⑫EXCELファイルを閉じる wb.Close
ここでは、開いたEXCELファイルを閉じています。
全てのシート名を「メイン」シートに記入し終わると、EXCELファイルを閉じます。
⑬ワークブックをクリアする
'⑬ワークブックをクリアする Set wb = Nothing
ここでは、ワークブックオブジェクトをクリアしています。
⑭FileSystemObjectをクリアする
'⑭FileSystemObjectをクリアする Set fso = Nothing
ここでは、使い終わったFileSystemObjectをクリアしています。
結果として、指定されたフォルダに存在するEXCELファイル全ての全シートを調べることができるようになりました。
それでは、シート名一覧を取得するツールを作って、作業を効率化しましょう。
以上です。
最後まで読んでいただきありがとうございました。
この記事をシェアしていただけると喜びます。
