フォルダ内の複数ブック×全部のシート名をセットで取得
エクセルのフォルダやワークブック、ワークシートは簡単に作成する事が出来て自由に名前を決める事ができます。
多くの方にはメリットになるのですがグループで使うとなるとデメリットになる事もあります。
会社等集団でデータを扱う際は規則を決めておかないとフォルダ内に色々な名前のデータが混在してしまいます。
タイミングをみて「不要なデータの削除」＆「残データの処置」を考えたいのですがまずは現状把握が必要です。
- フォルダ内のワークブックの名前を取得したい
- ワークブックの中にあるワークシートの名前を取得したい
- フォルダ内の複数のワークブックと全部のワークシート名をセットで取得したい
箇条書きに挙げたように棚卸を兼ねて情報を収集、取得したいと思っても手作業だと大変ですよね。
「やり方は分かっているけど工数も分かっているのでやらない」という仕事ですね。
「費用対効果が・・・」など作業をしなくても良いような理屈を自分で探す事になります。
「工数がかかるからやらない」というのは非常に残念な思考です。このような事例こそVBAで簡単に片付けましょう。
対象物（ワークブック、ワークシートの数）次第ですがマクロの方が手作業と比べて各段に速く作業できます。
「フォルダ内の複数のワークブックと全部のワークシート名をセットで取得」を実践してみます。
この本で勉強しました
フォルダやファイルの取り扱いについて勉強している皆様。
きっとご自身の仕事をマクロに置き換える為に試行錯誤している事でしょう。
そんな時は事例集（サンプルコード）のようなものを読んでいただくと視野が広がり手かがりを得る事ができます。
私は会社の机の上にこの本を置いています。行き詰った時に読んでアイデアをもらっています。
電子書籍（Kindle）もおすすめです。この本は大丈夫ですが大きい書籍は電子書籍で購入する様にしています。
PC内でVBEと電子書籍を並べて表示させながら作業をすると作業効率が劇的にあがりますよ。
関連記事
こちらの記事ではフォルダ内のファイル名を全て取得するという作業を自動化しました。
Excel以外の拡張子についてもファイル名を取得できる仕様です。
フォルダ内のファイルにアクセスマクロでフォルダ内のファイル名を取得｜Excel以外の拡張子にも対応
準備：フォルダ内に1000個のワークブックを用意
事例に取り組む為にはまず環境を用意する必要があります。
まずは「1つのフォルダの中に1000個のワークブックを用意」します。
用意するとは書いたのですが「この作業自体が面倒」ですね。VBAで準備しましょう。
コード
これでワークブックを1000個用意する事ができます。ファイル名はdata*.xlsxです。
*（アスタリスク）には変数「n」で追番が入る様になっています。詳細はコードをご覧ください。
Sub フォルダ内に1000個のワークブックを用意()
Dim wb As Workbook 'ワークブックを格納
Dim n As Long 'シート名の追番
Application.ScreenUpdating = False
Workbooks.Add '新規でワークブックを立ち上げて変数に格納する
Set wb = ActiveWorkbook
'このループの中で1000個のファイルを作る
For n = 1 To 1000
wb.SaveAs FileName:=ThisWorkbook.Path & "\test\" & "data" & n & ".xlsx", _
WriteResPassword:="password", _
Password:="password"
'パスワード関連のパラメータは必要に応じて設定／削除してください
Next
wb.Close 'ワークブックを閉じる
Application.ScreenUpdating = True
End Subご注意ください
SaveAsメソッドを使う時の注意点です。基本的にパラメータは「FileName」だけで運用してください。
コード内でパラメータ「PassWord」、「WriteResPassword」を使っていますが通常は不要です。
ポイントを解説
このコードのポイントはオブジェクト変数とSaveAsメソッドです。
オブジェクト変数
オブジェクト変数についてはこちらの記事で詳しく解説しています。
オブジェクト変数VBAで転記｜ブック間で文字列をコピペするマクロの作り方を教えます
SaveAsメソッド
構文とパラメータについて説明します。まずは構文です。
SaveAs (FileName、 FileFormat、 Password、 WriteResPassword、 ReadOnlyRecommended、 CreateBackup、 AccessMode、 ConflictResolution、 AddToMru、 TextCodepage、 TextVisualLayout、 Local)
パラメータは沢山ありますが全て省略できます。しかしFileNameだけは指定しましょう。
理由は「多くの事例でどこにどんな名前で保存するかを指定する為」です。パラメータ無で使う事はまず無いです。
確認ですがFileNameというのはフルパス（パスとファイル名）です。（パスの詳細はこちら）
FileNameの他に使いそうなパラメータを2つ紹介しておきます。残りはMicrosoftのリンクで確認してください。
| 番号 | パラメータ― | 説明 |
|---|---|---|
| 1 | FileName | 保存する際の場所とファイル名（ファイル名だけの時はカレントディレクトリに保存） |
| 2 | PassWord | ファイルを開く時のパスワードを設定 |
| 3 | WriteResPassword | 読み取り専用を解除する為のパスワードを設定 |
2番目のPassWordを設定するとワークブックを開いた際画像のようなメッセージボックスが表示されます。
3番目のWriteResPasswordを設定するとワークブックを開いた際画像のようなメッセージボックスが表示されます。
アウトプット
実行時は保存先フォルダをPCの画面に表示させてください。ブックがリアルタイムで増えていくのが分かります。
ブックは1000個あるので少し時間がかかりますご了承ください。最終的にフォルダ内は以下画像の様になります。
では続いて「フォルダ内の複数のワークブックと全部のワークシート名をセットで取得」していきます。
実践：フォルダ内の複数ブックと全部のシート名をセットで取得
ではフォルダ内のワークブックを全部開いてワークブック＆シートの名前をセットで取得していきます。
用意されたブックは全てSheet1のみです。よってワークブックとワークシートは1対1の関係で書き出されます。
しかしこのコードであればワークブック内に複数のシートがあっても全てブックとセットで取り出す事が出来ます。
ワークブックを開かない方法もありますが上級者様向けになります。ここでは紹介しません。
コード
コードはこちらになります。1000個のワークブックを開閉させるのでそれなりに時間がかかります。
私のPCでは6分程度かかりました。「手作業よりは楽」という気持ちで気長にお待ちいただく事をおすすめします。
Sub フォルダ内の複数ブックと全部のシート名をセットで取得()
Dim pth As String 'ファイルパスを格納
Dim file As String 'ファイル名を格納
Dim mywb As Workbook
Dim myws As Worksheet
Dim wb As Workbook
Dim ws As Worksheet
Dim r As Long: r = 1 '行
Dim c As Long: c = 1 '列
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'現在のワークブックとワークシートを変数に格納（文字列の貼り付け先を指示する為）
Set mywb = ThisWorkbook
Set myws = mywb.ActiveSheet
'パスとファイルを変数に格納
pth = ThisWorkbook.Path & "\test\"
file = Dir(pth & "*.xlsx")
Do While Len(file) > 0
Set wb = Workbooks.Open(pth & file)
For Each ws In wb.Worksheets
myws.Cells(r, c) = wb.Name
myws.Cells(r, c + 1) = ws.Name
r = r + 1
Next
Workbooks(wb.Name).Close
'Set wb = Nothing
'Nothingはローカルウインドウでオブジェクト変数が空になるのを見る為に用意
file = Dir()
Loop
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub配列は使わないのか
配列を使うとセルへのアプローチを減らせます。少しだけですが作業時間を改善する事ができます。
改善が見込まれますが本記事では配列を使いません。理由は今回のケースでは「効果しろ」が少ないからです。
今回のコードにおける作業時間のボトルネックはブックの開閉時間です。
ブックの開閉時間は配列では解決できない要素ですので今回は配列を使っていません。
配列を用いる事で作業時間が改善されるのはセルへのアプローチが多くなる（十万セル前後）コードです。
よって配列はケースによって使い分ける事をおすすめします。（配列が気になる方はこちらにお進みください）
ポイントを解説
ポイントはDir関数とDo While ～ Loopステートメント、For Each ～ Nextステートメントです。
Dir関数、Do While ～ Loopステートメント
Dir関数、Do While ～ Loopステートメントは主にフォルダ内のブックの取り回しを担当しています。
各要素の使い方はこちらの記事で詳しく解説しています。
Dir関数とDo While ～ LoopVBA｜Dir関数を使ってフォルダ内の複数ファイルを順番に処理する
For Each ～ Nextステートメント
For Each ～ Nextステートメントは主にブック内のシートの取り回しを担当しています。
使い方はこちの記事で詳しく解説しています。
For Each ～ Nextマクロで特定文字を含むシートを選択｜For Each ～ Next & Like
アウトプット
今回用意した全てのワークブックにはSheet1しか存在しないのでB列は全部同じ値です。
皆様ご自身で作業する際は複数のワークシートを持ったワークブックで試してみてください。
試しにdata1.xlsxだけSheet1からSheet5まで作って再度データを取得してみました。
A列のブック名はA.xlsxでB列のシート名はそれぞれのシート名を取得出来ている事が分かります。（青枠）
まとめ
「フォルダ内の複数のワークブックと全部のワークシート名をセットで取得」という作業を実践しました。
ループが絡んできますので少し難しい作業になります。しかし全部手作業でやると思えば勉強できますよね。
今回の仕事ができると応用で「既存のワークブックの名前を好きなように変える」という様な事もできます。
フォルダやファイル、シートの取り扱いが分かってくるとVBAが楽しくなってきますよ。
参考：再帰呼び出しを使う
今回は単一フォルダ内にあるワークブック名とワークシート名を取得するという作業を改善しました。
実際の仕事においてはフォルダ内にフォルダが用意されていたりフォルダ毎に階層が異なる事が多いです。
そんな時は再帰呼び出しを使うとフォルダの数や階層に関係なく全てのオブジェクトにアクセスできますよ。
