はじめに
複数のシートを含むExcelブックを整理するとき、最初に確認したいのが、そのブックにどのようなシートが含まれているかです。
シート数が少なければ画面上で確認できますが、数が増えると、目視だけでは全体を把握しにくくなります。
そこで今回は、指定したExcelファイルを選択し、そのブックに含まれるワークシート名を一覧として取得するVBAマクロを作成します。
このマクロを使うと、対象ブックを直接編集せずに、別のマクロ有効ブックへシート名の一覧を出力できます。
目的
今回の目的は、指定したExcelファイルについて、次の情報を一覧化することです。
- シート番号
- シート名
出力結果は、マクロを保存しているブック内のシート名一覧シートへ書き出します。
実行環境・前提条件
この記事では、次の環境を前提とします。
- Windows版Microsoft Excel
- VBAを実行できるデスクトップ版Excel
- マクロ有効ブック形式（
.xlsm） - 対象ファイルはExcel形式（
.xls、.xlsx、.xlsm、.xlsb）
Web版Excelでは、VBAマクロは実行できません。
このマクロでできること
このマクロを実行すると、次の処理が行われます。
- ファイル選択ダイアログを表示する
- シート名を取得したいExcelファイルを選択する
- マクロを保存しているブック内に
シート名一覧シートを作成する - 選択したファイルに含まれるワークシート名を取得する
- シート番号とシート名を一覧表示する
- 対象ファイルを保存せずに閉じる
対象ファイルは読み取り専用で開くため、元のExcelファイルは変更されません。
出力イメージ
出力結果は、次のような形式になります。
| シート番号 | シート名 |
|---|---|
| 1 | Summary |
| 2 | Data_01 |
| 3 | Data_02 |
| 4 | Checklist |
VBAコード
以下のコードを、標準モジュールへそのまま貼り付けて使用できます。
Option Explicit
Sub ExtractSheetNamesFromSelectedFile()
Dim wbTarget As Workbook
Dim wsOutput As Worksheet
Dim wbSheet As Worksheet
Dim targetPath As Variant
Dim i As Long
On Error GoTo ErrorHandler
' ファイル選択ダイアログを表示する
targetPath = Application.GetOpenFilename( _
FileFilter:="Excel Files (*.xls; *.xlsx; *.xlsm; *.xlsb), *.xls; *.xlsx; *.xlsm; *.xlsb", _
Title:="シート名を取得するExcelファイルを選択してください" _
)
' キャンセルされた場合は処理を終了する
If targetPath = False Then
Exit Sub
End If
Application.ScreenUpdating = False
Application.DisplayAlerts = False
' 既存の「シート名一覧」シートがある場合は削除する
On Error Resume Next
ThisWorkbook.Worksheets("シート名一覧").Delete
On Error GoTo ErrorHandler
Application.DisplayAlerts = True
' 出力用シートを作成する
Set wsOutput = ThisWorkbook.Worksheets.Add( _
After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count) _
)
wsOutput.Name = "シート名一覧"
' 見出しを設定する
wsOutput.Cells(1, 1).Value = "シート番号"
wsOutput.Cells(1, 2).Value = "シート名"
' 対象ファイルを読み取り専用で開く
Set wbTarget = Workbooks.Open( _
Filename:=CStr(targetPath), _
ReadOnly:=True, _
UpdateLinks:=False _
)
' 対象ブックのワークシート名を一覧へ出力する
i = 2
For Each wbSheet In wbTarget.Worksheets
wsOutput.Cells(i, 1).Value = i - 1
wsOutput.Cells(i, 2).Value = wbSheet.Name
i = i + 1
Next wbSheet
' 対象ブックを保存せずに閉じる
wbTarget.Close SaveChanges:=False
Set wbTarget = Nothing
' 表示を整える
With wsOutput
.Rows(1).Font.Bold = True
.Columns("A:B").AutoFit
.Range("A1:B" & i - 1).Borders.LineStyle = xlContinuous
.Activate
End With
Application.DisplayAlerts = True
Application.ScreenUpdating = True
MsgBox "指定したファイルからシート名を取得しました。", vbInformation
Exit Sub
ErrorHandler:
' 対象ブックが開いている場合は保存せずに閉じる
If Not wbTarget Is Nothing Then
wbTarget.Close SaveChanges:=False
End If
Application.DisplayAlerts = True
Application.ScreenUpdating = True
MsgBox _
"処理中にエラーが発生しました。" & vbCrLf & _
"エラー番号: " & Err.Number & vbCrLf & _
"内容: " & Err.Description, _
vbExclamation
End Sub
コードを保存する場所
1. マクロ有効ブックを作成する
新しいExcelファイルを開き、名前を付けて保存を選択します。
ファイル形式は、次を指定してください。
Excel マクロ有効ブック（*.xlsm）
2. VBAエディターを開く
ExcelでAlt + F11を押します。
VBAエディターが開いたら、上部メニューから次を選択します。
挿入
→ 標準モジュール
3. コードを貼り付ける
表示されたコード画面へ、この記事のVBAコードをすべて貼り付けます。
貼り付け後、Ctrl + Sで保存します。
4. マクロを実行する
Excelへ戻り、Alt + F8を押します。
マクロ一覧から、次を選択します。
ExtractSheetNamesFromSelectedFile
実行を押すと、ファイル選択ダイアログが表示されます。
コードの主なポイント
GetOpenFilenameで対象ファイルを選択する
次の処理で、ファイル選択ダイアログを表示しています。
targetPath = Application.GetOpenFilename( _
FileFilter:="Excel Files (*.xls; *.xlsx; *.xlsm; *.xlsb), *.xls; *.xlsx; *.xlsm; *.xlsb", _
Title:="シート名を取得するExcelファイルを選択してください" _
)
Application.GetOpenFilenameは、ファイルを直接開くのではなく、選択されたファイルのパスを返します。
キャンセルされた場合は、Boolean型のFalseが返ります。
そのため、変数targetPathはStringではなく、Variantとして宣言しています。
Dim targetPath As Variant
対象ブックを読み取り専用で開く
対象ファイルは、次の処理で開いています。
Set wbTarget = Workbooks.Open( _
Filename:=CStr(targetPath), _
ReadOnly:=True, _
UpdateLinks:=False _
)
ReadOnly:=Trueを指定することで、対象ファイルを読み取り専用で開きます。
また、UpdateLinks:=Falseを指定し、外部リンクの更新を行わないようにしています。
Worksheetsを使ってワークシート名を取得する
シート名は、次の処理で取得しています。
For Each wbSheet In wbTarget.Worksheets
wsOutput.Cells(i, 1).Value = i - 1
wsOutput.Cells(i, 2).Value = wbSheet.Name
i = i + 1
Next wbSheet
ここでは、SheetsではなくWorksheetsを使用しています。
Sheetsには、通常のワークシートだけでなく、グラフシートなどが含まれる場合があります。
今回の目的はワークシート名の取得であるため、Worksheetsを使用しています。
既存の一覧シートを削除する
すでにシート名一覧シートが存在する場合は、削除してから新しい一覧を作成します。
Application.DisplayAlerts = False
On Error Resume Next
ThisWorkbook.Worksheets("シート名一覧").Delete
On Error GoTo ErrorHandler
Application.DisplayAlerts = True
Application.DisplayAlerts = Falseを指定することで、シート削除時の確認メッセージを表示しないようにしています。
エラー発生時の処理
対象ファイルを開いた後にエラーが発生すると、ファイルが開いたまま残ることがあります。
そのため、エラー処理内で、対象ブックが開いている場合は保存せずに閉じます。
If Not wbTarget Is Nothing Then
wbTarget.Close SaveChanges:=False
End If
確認結果
マクロを実行し、対象ファイルを選択すると、マクロを保存しているブック内にシート名一覧シートが作成されます。
次のように表示されれば成功です。
- A列にシート番号が表示されている
- B列にシート名が表示されている
- 1行目が太字になっている
- A列とB列の幅が自動調整されている
- 対象ファイルが保存されずに閉じている
情報整理との関係
シート名の一覧化は、小さな処理ですが、Excel内の情報構造を把握するための基本的な作業です。
複数シートを含むブックでは、最初にシート構成を確認しておくことで、次の作業へ進みやすくなります。
Excelブックを選択
↓
シート構成を一覧化
↓
対象となるシートを確認
↓
統合・分類・比較の方法を検討
↓
再利用しやすいデータへ整理
情報を再利用しやすい形へ整理するためには、すぐに統合や加工を始めるのではなく、最初に対象となる情報の範囲と構造を把握することが重要です。
注意点・つまずいた点
マクロを保存するブックと対象ブックは別です
このマクロでは、シート名の一覧をThisWorkbookへ出力しています。
ThisWorkbookは、マクロコードを保存しているExcelブックを意味します。
選択した対象ファイルへ一覧を追加する処理ではありません。
既存のシート名一覧シートは削除されます
マクロを再実行すると、マクロを保存しているブック内のシート名一覧シートは削除され、新しく作り直されます。
同じシートに手作業で追記した内容がある場合、その内容も削除されるため注意してください。
非表示シートも取得されます
Worksheetsコレクションには、表示中のシートだけでなく、非表示のワークシートも含まれます。
非表示シートを除外したい場合は、追加の条件分岐が必要です。
保護されたブックでは実行できない場合があります
マクロを保存しているブックの構造が保護されている場合、シートの追加や削除ができず、エラーになる可能性があります。
おわりに
今回は、指定したExcelファイルに含まれるワークシート名を、VBAで一括取得する方法を紹介しました。
シート名の一覧化は単純な作業ですが、複数シートを含むExcelブックの全体像を把握するうえで役立ちます。
情報を統合したり、比較したり、再利用可能な形へ整理したりする前に、まず対象となる情報の構造を確認することが重要です。
今後も、Excel、VBA、Pythonなどを使いながら、調査結果や業務上の情報を再利用しやすい形へ整理する方法を記録していきます。
きのとリサーチ
https://kinoto-research.jp/