イントロダクション
前回（第20回）は、ブックを開く・保存する・閉じるという、ブックの基本操作を取り上げた。
ただし、開くブックのファイル名を、コードに直接書いていた。
ところで、実務では、「このフォルダにある月別のブックを、すべて処理したい」という場面が多い。そのためには、フォルダの中にどんなファイルがあるのかを、マクロで調べる必要がある。
今回は、そのための2つの道具を取り上げよう。
Dir関数：フォルダ内のファイル名を、順番に取り出すFileDialog：ダイアログを表示して、フォルダやファイルを選んでもらう
次回（第22回）の集計マクロに向けた、準備の回となる。
準備：サンプルのフォルダとファイルを用意する
今回は、次のフォルダとファイルを準備しよう。
C:\test\月別売上というフォルダを作成しよう- Excelで新しいブックを3つ作り、それぞれ次のように入力して、フォルダの中に保存しよう
| ファイル名 | A1セル | B1セル |
|---|---|---|
売上_1月.xlsx | 1月 | 100 |
売上_2月.xlsx | 2月 | 200 |
売上_3月.xlsx | 3月 | 300 |
- メモ帳で、適当な内容でテキストファイル
メモ.txtを作り、同じフォルダに保存しよう
メモ.txt は、「Excel以外のファイルが混ざっている」場面を再現するためのものである。
なお、サンプルのブックは、書き込みが終わったら閉じておこう。
Dir関数の基本
Dir 関数は、指定した条件に合うファイル名を、1件ずつ返す関数である。
Dir関数Dir(パス) → 最初に見つかったファイル名を返す（見つからなければ空の文字列 "" ）Dir → 次のファイル名を返す（引数を省略して呼び出す）
第19回で使った If Dir(パス) = "" Then という存在確認は、「最初の1件を返す」という、この動作を利用したものである。まずは、最初の1件だけ取り出してみよう。
Sub 最初のファイル名を調べる()
Dim ファイル名 As String
ファイル名 = Dir("C:\test\月別売上\*.xlsx")
MsgBox "最初のファイル：" & ファイル名
End Sub*.xlsx の * は、「任意の文字列」を表すワイルドカードである。つまり、「拡張子が .xlsx のファイル」という条件になる。どのファイルが最初に返るかは、フォルダの中の並び順で決まる。
ここで、2点確認しておこう。
Dirが返すのは、ファイル名だけである。フォルダの場所（C:\test\月別売上\）は含まれない。ブックを開くときなどは、フォルダ & ファイル名とつなげる必要があるDirで調べられるのは、指定したフォルダの直下だけである。中にあるサブフォルダの、さらに中のファイルは対象にならない
すべてのファイル名を取り出す
2件目以降は、引数なしの Dir を呼び出すと取り出せる。見つかるファイルがなくなると "" が返ってくるので、それを繰り返しの終了条件にする。
【注意】
このマクロの出力はDebug.Printなので、画面には出ず、VBEの「イミディエイトウィンドウ」に表示される。Ctrl + Gで開こう。なお、VBEのメニュー[表示]→[イミディエイト ウィンドウ]でも開ける。
Sub ファイル名を一覧にする()
Const フォルダ As String = "C:\test\月別売上\"
Dim ファイル名 As String
ファイル名 = Dir(フォルダ & "*.xlsx")
Do While ファイル名 <> ""
Debug.Print ファイル名
ファイル名 = Dir
Loop
End Sub流れは次のとおりである。
- 最初に
Dir(フォルダ & "*.xlsx")で、1件目を取り出す ファイル名が""でない間、処理を繰り返す- 繰り返しの最後に、引数なしの
Dirで、次のファイル名を取り出す
実行して、イミディエイトウィンドウ（Ctrl + G）を見てみよう。売上_1月.xlsx、売上_2月.xlsx、売上_3月.xlsx の3つが表示されるはずである。メモ.txt は条件に合わないので、表示されない。なお、表示される順番は、環境によって異なることがある。
ワイルドカードで条件を指定する
Dir の条件には、次の2種類のワイルドカードが使える。
*：任意の長さの文字列（0文字以上）?：任意の1文字
| 条件 | 意味 | 見つかるファイルの例 |
|---|---|---|
*.xlsx | 拡張子が .xlsx のファイル | 売上_1月.xlsx、売上_2月.xlsx、売上_3月.xlsx |
売上_*.xlsx | 売上_ で始まる .xlsx | 同上 |
売上_?月.xlsx | 売上_ ＋ 1文字 ＋ 月.xlsx | 同上（売上_10月.xlsx は、? が1文字なので対象外） |
*.* | すべてのファイル | メモ.txt を含む、すべて |
ひとつ注意がある。*.xls と指定すると、.xlsx のファイルまで見つかってしまうことがある
。古い形式の .xls だけを探したいときは、拡張子の確認を、別に行う。
一覧をシートに書き出す
一覧をイミディエイトウィンドウに表示するだけでなく、シートに書き出すこともできる。
Sub ファイル一覧をシートに書き出す()
Const フォルダ As String = "C:\test\月別売上\"
Dim ws As Worksheet
Dim ファイル名 As String
Dim 行 As Long
Set ws = ThisWorkbook.Worksheets.Add
ws.Range("A1").Value = "ファイル名"
行 = 2
ファイル名 = Dir(フォルダ & "*.xlsx")
Do While ファイル名 <> ""
ws.Cells(行, 1).Value = ファイル名
行 = 行 + 1
ファイル名 = Dir
Loop
End Sub新しいシートを作る Worksheets.Add は、第5回で学んだ方法である。今回は ThisWorkbook.Worksheets.Add と書いているので、マクロ有効ブックの中にシートが追加される。位置を指定していないため、実行したときに表示していたシートのすぐ左側に、Sheet2 のような名前で追加され、そのシートが画面に表示される。
なお、実行するたびに新しいシートが追加されるので、手動で削除しておこう。
一時ファイルを除外する
Excelでブックを開いていると、同じフォルダに ~$売上_1月.xlsx のような名前の一時ファイル（ロックファイル）が作られる。通常は隠しファイルなので、Dir の結果には出てこないが、条件によって一覧に混ざる可能性がある。そこで、~$ で始まるファイル名は処理の対象から外す書き方を、習慣にしておくと安心である。
Sub 一時ファイルを除いて一覧にする()
Const フォルダ As String = "C:\test\月別売上\"
Dim ファイル名 As String
ファイル名 = Dir(フォルダ & "*.xlsx")
Do While ファイル名 <> ""
If Left(ファイル名, 2) <> "~$" Then
Debug.Print ファイル名
End If
ファイル名 = Dir
Loop
End Sub【注意】実行しても、シートやメッセージボックスには何も表示されない。
結果は、VBEの「イミディエイトウィンドウ」（Ctrl + Gで開く）に出力される。サンプルのフォルダ（売上_1月.xlsx、売上_2月.xlsx、売上_3月.xlsx、メモ.txt）なら、次のように1行ずつ表示される。
売上_1月.xlsx
売上_2月.xlsx
売上_3月.xlsx
Left(ファイル名, 2) は、文字列の先頭から2文字を取り出す関数である。
【注意！】繰り返しの途中で、別のDirを使ってはいけない
Dir には、とても大切な注意点がある。
Dir は、「今どこまで探したか」を1つだけ覚えている。そのため、一覧を取り出している繰り返しの途中で、別の Dir(パス) を実行すると、それまでの探索の続きが失われる。
' ==========================================
' 【NG例】繰り返しの途中で Dir を使う関数を呼んでいる場合
' ==========================================
Do While ファイル名 <> ""
' 中で Dir(パス) を使っているため、大元のループの検索状態が破壊される
Set wb = ブックを安全に開く(フォルダ & ファイル名)
' （ここに何らかの処理が入る）
' 期待した「1件目の続き」ではなく、別の探索の続きになってしまい無限ループやバグの原因に
ファイル名 = Dir
Loop第20回で作った ブックを安全に開く は、中で Dir(パス) を使って存在確認をしている。これを繰り返しの中で呼ぶと、一覧が途中で終わってしまい、一部のファイルが処理されなくなる。
対策は、先にファイル名をすべて取り出して、どこかに保存しておき、その後で1件ずつ処理することである。取り出した後なら、Dir を自由に使える。
ファイル名をまとめて取得するFunction
ファイル名の保存先には、Collection（コレクション）を使うのが便利である。第1回で学んだ Workbooks や Worksheets と同じ「オブジェクトの集まり」を、自分で作れるものだと考えるとよい。Add で要素を追加でき、Count で件数が分かる。
先ほどの内容を、第12回で学んだ Function にまとめてみよう。
Function ファイル一覧を取得する(フォルダ As String, パターン As String) As Collection
Dim 一覧 As New Collection
Dim ファイル名 As String
ファイル名 = Dir(フォルダ & パターン)
Do While ファイル名 <> ""
If Left(ファイル名, 2) <> "~$" Then
一覧.Add ファイル名
End If
ファイル名 = Dir
Loop
Set ファイル一覧を取得する = 一覧
End Function使うときは、次のようにする。For Each で取り出す変数は、Collection の場合は Variant で宣言する。
Sub 一覧を取得して表示する()
Dim 一覧 As Collection
Dim ファイル名 As Variant
Set 一覧 = ファイル一覧を取得する("C:\test\月別売上\", "*.xlsx")
MsgBox 一覧.Count & " 件のブックが見つかりました。"
For Each ファイル名 In 一覧
Debug.Print ファイル名
Next ファイル名
End Subメッセージボックス に「3 件のブックが見つかりました。」と表示され、イミディエイトウィンドウに3つのファイル名が表示されれば成功である。この方法なら、For Each の中で ブックを安全に開く のような、Dir を使うプロシージャを呼んでも問題ない。
【注意】
これまでにマクロを何度も実行していると、イミディエイトウィンドウの表示が乱れる可能性がある。実行前にイミディエイトウィンドウの中をCtrl + A → Deleteで消しておくとよい。
FileDialogでフォルダを選んでもらう
ここまでは、フォルダの場所を、コードに直接書いていた。しかし、他の人が使うマクロでは、フォルダを自分で選べると便利である。そのために使うのが、FileDialog である。
フォルダを選択するダイアログApplication.FileDialog(msoFileDialogFolderPicker)
Function フォルダを選ぶ() As String
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "フォルダを選択してください"
If .Show = -1 Then
フォルダを選ぶ = .SelectedItems(1)
Else
フォルダを選ぶ = ""
End If
End With
End Function.Title：ダイアログのタイトルを指定する.Show：ダイアログを表示する。[OK]で閉じると-1、[キャンセル]で閉じると0が返る.SelectedItems(1)：選択されたフォルダのパスを取り出す
キャンセルされたときは、空の文字列 "" を返す設計にしている。呼び出す側で、これを確認すれば、キャンセルにも対応できる。
以下のマクロを実行してみよう。
Sub 選んだフォルダのブックを一覧にする()
Dim フォルダ As String
Dim 一覧 As Collection
Dim ファイル名 As Variant
フォルダ = フォルダを選ぶ()
If フォルダ = "" Then
MsgBox "キャンセルされました。"
Exit Sub
End If
' 末尾に「\」がなければ付ける
If Right(フォルダ, 1) <> "\" Then フォルダ = フォルダ & "\"
Set 一覧 = ファイル一覧を取得する(フォルダ, "*.xlsx")
MsgBox 一覧.Count & " 件のブックが見つかりました。"
For Each ファイル名 In 一覧
Debug.Print ファイル名
Next ファイル名
End Subダイアログで選んだフォルダのパスは、末尾に \ が付かないことが多い。そのまま ファイル名 とつなげると、C:\test\月別売上売上_1月.xlsx のような間違ったパスになってしまう。そこで、Right(フォルダ, 1) で最後の1文字を確認し、\ でなければ付け足している。
実行すると、次の順に動く。
１．フォルダ選択のウインドウ開く：タイトルが「フォルダを選択してください」のエクスプローラーに似たウインドウが表示される（フォルダを選ぶFunctionの動作である）。C:\test\月別売上を選んで[OK]を押そう。
２．キャンセルした場合：「キャンセルされました。」というメッセージが出て、マクロが終わる。
３．[OK]を押した場合：選んだフォルダのパスの末尾に\がなければ付け足す。
４．ブックを検索する：ファイル一覧を取得するが、そのフォルダ直下の.xlsxファイルを探す。（~$で始まる一時ファイルは除外される）
５．見つかったブックの件数がメッセージで表示される：サンプルのフォルダなら「3 件のブックが見つかりました。」と表示される。
６．ファイル名が出力される：[OK]を押すと、売上_1月.xlsx、売上_2月.xlsx、売上_3月.xlsxが、イミディエイトウィンドウに1行ずつ表示される。
選んだフォルダに.xlsxが1つもなければ、「0 件のブックが見つかりました。」と表示され、イミディエイトウィンドウには何も出ない。たとえばC:\testを選ぶと、第20回で作った売上.xlsxなどが対象になる。また、中にあるサブフォルダの.xlsxは、一覧に含まない。
なお、このマクロを動かすには、フォルダを選ぶとファイル一覧を取得するの2つのFunctionが、同じ標準モジュールに入っている必要がある。どちらかが欠けていると、「Sub または Function が定義されていません」というコンパイルエラーになる。
ファイルを1つ選んでもらう
フォルダではなく、ファイルを選んでもらいたいときは、msoFileDialogFilePicker を使う。
Sub ファイルを選ぶ()
With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
.Filters.Clear
.Filters.Add "Excelブック", "*.xlsx"
If .Show = -1 Then
MsgBox "選んだファイル：" & .SelectedItems(1)
Else
MsgBox "キャンセルされました。"
End If
End With
End Sub.AllowMultiSelect：Trueにすると、複数のファイルを選べる（今回はFalse）.Filters.Add：選べるファイルの種類を、絞り込む
実行すると、次の順に動く。
１．ファイル選択のウィンドウが開く：フォルダ選択と同じように、エクスプローラーに似たウィンドウが表示される（参照）。今回は、フォルダではなくファイルを選ぶウィンドウである。
２．表示されるファイルが絞り込まれる：.Filters.Clearで標準の絞り込みを消し、.Filters.Add "Excelブック", "*.xlsx"で「Excelブック」という種類（*.xlsx）だけを追加していく。そのため、.xlsxのファイルだけが一覧に表示され、メモ.txtなどは表示されない。
３．選べるのは1つだけ：.AllowMultiSelect = Falseなので、複数のファイルは同時に選べない。
４．選んで決定した場合：C:\test\月別売上\売上_1月.xlsxのようなフルパスが、「選んだファイル：…」というメッセージで表示される。
５．キャンセルした場合：「キャンセルされました。」と表示される。
【注意】
決定のボタンは「開く」と表示されるが、ブックが開くわけではない。選んだファイルのパスが、取得されるだけである。.xlsmや.xlsのファイルも表示したいときは、拡張子をセミコロンでつなげて、
.Filters.Add “Excelブック”, “*.xlsx;*.xlsm;*.xls”
ように指定する。
まとめ
今回は、フォルダ内のファイルを調べる方法を学んだ。
Dir(パス)で最初のファイル名を、引数なしのDirで次のファイル名を取り出せる。- 戻り値はファイル名だけで、見つからなくなると
""が返る。 *と?のワイルドカードで、条件を指定できる。~$で始まる一時ファイルは、除外しておくと安全である。- 繰り返しの途中で、別の
Dir(パス)を使うと、一覧が壊れる。先にファイル名をすべて取り出し、Collectionなどに保存してから処理する。 FileDialogを使えば、フォルダやファイルを選んでもらえる。キャンセルへの対応も忘れない。
次回（第22回）は、ブック・ファイル操作編の最終回として、これまでの内容をまとめた実践編に挑戦する。フォルダ内のブックを順番に開き、決まったセルの値を、集計用のシートに転記するマクロを作ろう。第12回のFunctionやエラー処理編の後始末も、ここで活躍する。
コメント