Home » エクセルマクロ・Excel VBAの使い方 » Workbooks・Workbook » VBAでブックを開きそのブックに含まれるシート名一覧を作成する

VBAでブックを開きそのブックに含まれるシート名一覧を作成する

動作検証バージョン：Windows 11 Home + 64bit Excel バージョン 2407（ビルド17730.20000クイック実行）ベータチャネル

検索キーワード
「vba ほかのブックのシート一覧取得」
をきっかけにして、シート名一覧を取得したいブックを手作業で開いておいてから実行するExcelマクロをご紹介しました。

実務では、ブックを開く処理もマクロに組み込んでおきたいというニーズも多そうです。

［スポンサードリンク］

ブックを開きシート名一覧を作成するサンプルマクロ

以下のExcelマクロを実行すると、Cドライブtempフォルダーのsample.xlsxブックを開いて、そのブックのシート名一覧を、このマクロの書かれているブックのアクティブシートに作成します。

Sub ブックを開いてシート名一覧を作成する()
　Dim bk As Workbook
　Set bk = Workbooks.Open("C:\temp\sample.xlsx")
　
　Dim i As Long
　For i = 1 To bk.Sheets.Count
　　ThisWorkbook.ActiveSheet.Cells(i, "A").Value _
　　　　= bk.Sheets(i).Name
　Next

　ThisWorkbook.Activate
　MsgBox "指定されたブックのシート名一覧を" & ActiveSheet.Name & "に作成しました。"

End Sub

サンプルマクロで行っている処理

サンプルマクロで行っている処理は以下のとおりです。

Workbooks.Openで対象ブックを開き変数に代入

ご紹介済みの、ブックを事前に開いておくタイプのマクロでは、オブジェクト変数bkに代入する行が

Set bk = Workbooks("sample.xlsx")

となっていましたが、上記のマクロでは

Set bk = Workbooks.Open("C:\temp\sample.xlsx")

と、Workbooks.Openメソッドを使ってブックを開く処理になっています。

シート名一覧の作成後の処理

シート名一覧を作成するFor～Nextループ部分に、違いはありません。
For～Nextループを抜けたあとに、

ThisWorkbook.Activate
MsgBox "指定されたブックのシート名一覧を" & ActiveSheet.Name & "に作成しま

といった行を追加しています。

シート名一覧の出力には関係ありませんが、ブックを開く処理をマクロに入れると、最後に開かれたブックがアクティブな状態になってしまうので、シート名一覧を作成したこのブックをアクティブにしてメッセージを表示しています。

開いたブックを閉じてもいいという場合も多そうです。
その場合は、

ThisWorkbook.Activate

を

bk.Close

としてください。

エラー処理を入れたサンプルマクロ

Cドライブtempフォルダーにsample.xlsxブックが存在しなければ実行時エラーが発生します。

それを回避するのであれば、以下のようにエラー処理を追加してください。

Sub ブックを開いてシート名一覧を作成する__エラー処理入り()
On Error GoTo ErrHandl
　
　Dim bk As Workbook
　Set bk = Workbooks.Open("C:\temp\sample.xlsx")
　
　Dim i As Long
　For i = 1 To bk.Sheets.Count
　　ThisWorkbook.ActiveSheet.Cells(i, "A").Value _
　　　　= bk.Sheets(i).Name
　Next

　ThisWorkbook.Activate
　MsgBox "指定されたブックのシート名一覧を" & ActiveSheet.Name & "に作成しました。"

Exit Sub
ErrHandl:
　Select Case Err.Number
　　Case 1004
　　　MsgBox "指定されたブックが見つかりませんでした。"
　　Case Else
　　　MsgBox Err.Description & vbCrLf & Err.Number
　End Select
　Err.Clear
End Sub

［スポンサードリンク］