毎月届く取引先別・拠点別のExcelファイルを、1つずつ開いてはコピペで集計する作業を続けていませんか。ファイルが10個、20個と増えるほど、この作業にかかる時間もミスのリスクも比例して増えていきます。この記事では、指定したフォルダ内のExcelファイルを自動で1つずつ開いて処理してくれる「Dir関数」の使い方を、図解を交えて解説します。
「1つずつ手作業で開いて集計」はいずれ限界がくる
取引先ごとに送られてくる注文書や、拠点ごとの日報を1つのファイルにまとめる作業は、多くの職場で発生する定番業務です。ファイルが数個のうちは手作業でも何とかなりますが、取引先が増えたり月次の対象期間が長くなったりすると、開く順番を間違えたり、1つのファイルを集計し忘れたりといったミスが起こりやすくなります。
さらに厄介なのは、この作業が「今月は12個のファイルがあるが、来月は15個かもしれない」というように、対象ファイルの数があらかじめ決まっていない点です。ファイル名を1つずつコードに書いて開くマクロを作っても、ファイルの増減にはそのままでは対応できません。この問題を解決してくれるのが、フォルダの中身を順番に調べてくれるDir関数です。
Dir関数の基本構文とファイル名を順番に取得する仕組み
Dir関数の基本的な使い方は次のとおりです。
Dim fileName As String
fileName = Dir("C:\Data\*.xlsx")
Do While fileName <> ""
Debug.Print fileName
fileName = Dir()
Loop
最初にDir("C:\Data\*.xlsx")のようにフォルダとワイルドカード（*.xlsxで「拡張子がxlsxのすべてのファイル」を意味します）を指定すると、そのフォルダの中で最初に見つかったファイル名が返ってきます。そのあと、引数を付けずにDir()を呼び出すたびに、次に見つかったファイル名が順番に返され、すべて調べ終わると空文字（""）が返ってきます。この性質を利用して、Do While fileName <> ""でループを組めば、フォルダ内のファイル数がいくつであっても、すべてのファイルを自動で処理できます。
下の図は、フォルダの中にあるExcelファイルの名前を、Dir関数がひとつずつ順番に取り出していく流れを表したものです。
取得したファイル名で各ブックを開いて集計する
ファイル名が取得できたら、それを使って実際にブックを開き、データを転記していきます。
Dim folderPath As String, fileName As String, wb As Workbook
folderPath = "C:\Data\"
fileName = Dir(folderPath & "*.xlsx")
Do While fileName <> ""
Set wb = Workbooks.Open(folderPath & fileName)
' ここでwbから集計用ブックへデータを転記
wb.Close SaveChanges:=False
fileName = Dir()
Loop
ポイントは、フォルダパスとファイル名をつなげたfolderPath & fileNameをWorkbooks.Openに渡してブックを開いていることです。開いたブックから必要なデータをコピーしたら、以前の記事で紹介した最終行の取得やFor Nextループを組み合わせて、集計用ブックの空いている行に転記していきます。処理が終わったらwb.Close SaveChanges:=Falseで元のファイルを変更せずに閉じ、次のファイルへと進みます。次の図は、フォルダ内の複数のブックが、1つの集計用ブックへとまとめられていく様子を表したものです。
実務で使う際の注意点
実際にこの仕組みを使う際は、いくつか気をつけたい点があります。まず、Excelでファイルを開いたままにしていると、そのファイルの一時ファイル（~$ファイル名.xlsx）も同じフォルダに作られ、Dir関数がそれも1つのファイルとして拾ってしまうことがあります。If Left(fileName, 2) <> "~$" Thenのような条件を入れて、一時ファイルを処理対象から除外しておくと安全です。
また、開いたブックは必ずCloseで閉じることも重要です。閉じ忘れたまま次のファイルを開き続けると、Excelが開きっぱなしのブックだらけになり、動作が重くなったり、意図せず上書き保存してしまう事故につながります。処理件数が多い場合はApplication.ScreenUpdating = Falseで画面の再描画を止めておくと、体感速度が大きく改善します。フォルダの中身が変わっても書き直しが不要なマクロができれば、月次の集計作業がぐっと楽になります。
コメント