この記事では、指定されたフォルダに存在するEXCELファイルの全シート名を取得し、一覧化する方法について説明しています。
VBAを利用すれば、フォルダに存在するファイルを全て取得でき、１つ１つEXCELファイルを手で開く必要はありません。
しかも、全てのシート名を間違いなく取得し、簡単に一覧化することができます。
それでは、指定したフォルダ配下の全てのEXCELファイルを対象に、全てのシート名を調べる機能を作成していきましょう。
困っている女性
困っている女性
困っている女性
解決する男性
解決する男性
困っている女性
◆似たような作業で、ファイル一覧を取得する方法については、以下の記事を参照してください。
【EXCEL：VBA】指定したフォルダに存在するファイル一覧を取得したい
◆他には、指定したフォルダ階層を一気に作成する方法については、以下の記事を参照してください。
【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）
標準モジュールを追加して、以下のソースを書きます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 |
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）を説明します。
①参照するシートを変数に格納する
|
1 2 |
'①参照するシートを変数に格納する Set shtMain = ThisWorkbook.Sheets("メイン") |
そうすることにより、入力するプログラムを短くすることができるからです。
何度も「ThisWorkbook.Sheets(“メイン”)」と入力するより、「shtMain」と入力するほうがコードが少なくてよいからです。
②データ入力されているA列の最終行を取得する
|
1 2 |
'②データ入力されているA列の最終行を取得する MaxRow = shtMain.Cells(shtMain.Rows.Count, 1).End(xlUp).Row |
何度も実行すると、前回実行時のEXCELファイル名が入力されているので、
処理の実行前にセルに入力されている値をクリアするために、
値が入力されている最終行を取得します。
最終行を取得することで、どこまでのセルをクリアするべきかがわかるからです。
③最終行が5行目以降なら、5行目以降のA列とB列のセルをクリアする
|
1 2 3 4 |
'③最終行が5行目以降なら、5行目以降のA列とB列のセルをクリアする If MaxRow >= 5 Then shtMain.Range("A5:B" & MaxRow).Clear End If |
その場合、A5セルからB列の最終行までをクリアします。
この処理によって、すでに入力されているファイル名とシート名をクリアされます。
④FileSystemObjectを変数に格納する
|
1 2 |
'④FileSystemObjectを変数に格納する Set fso = CreateObject("Scripting.FileSystemObject") |
「FileSystemObject」は、フォルダに存在するファイルを調べることに利用します。
⑤指定されているフォルダに存在するファイル数分処理を繰り返す
|
1 2 |
'⑤指定されているフォルダに存在するファイル数分処理を繰り返す For Each f In fso.GetFolder(shtMain.Range("A2")).Files |
⑥拡張子がxlsxのファイルのみ対象とする
|
1 2 |
'⑥拡張子がxlsxのファイルのみ対象とする If LCase(fso.getextensionName(f.Name)) = "xlsx" Then |
拡張子が「xlsx」のファイルのみを対象に、以降の処理を行います。
例えば、拡張子が「txt」なら、対象外となります。
⑦EXCELファイルを開く
|
1 2 |
'⑦EXCELファイルを開く Set wb = Workbooks.Open(shtMain.Range("A2") & "\" & f.Name) |
⑧存在するシート数分繰り返す
|
1 2 |
'⑧存在するシート数分繰り返す For i = 1 To wb.Sheets.Count |
⑨現在行を次の行に変更する
|
1 2 |
'⑨現在行を次の行に変更する nowRow = nowRow + 1 |
⑩ファイル名をA列にセットする
|
1 2 |
'⑩ファイル名をA列にセットする shtMain.Range("A" & nowRow) = f.Name |
⑪シート名をB列にセットする
|
1 2 |
'⑪シート名をB列にセットする shtMain.Range("B" & nowRow) = wb.Sheets(i).Name |
⑫EXCELファイルを閉じる
|
1 2 |
'⑫EXCELファイルを閉じる wb.Close |
全てのシート名を「メイン」シートに記入し終わると、EXCELファイルを閉じます。
⑬ワークブックをクリアする
|
1 2 |
'⑬ワークブックをクリアする Set wb = Nothing |
⑭FileSystemObjectをクリアする
|
1 2 |
'⑭FileSystemObjectをクリアする Set fso = Nothing |
結果として、指定されたフォルダに存在するEXCELファイル全ての全シートを調べることができるようになりました。
それでは、シート名一覧を取得するツールを作って、作業を効率化しましょう。
以上です。
最後まで読んでいただきありがとうございました。
この記事をシェアしていただけると喜びます。
