フォルダ内の Excel ファイルを一括で開いて1つにまとめる（Dir 関数）

どんな場面で使うか

各店舗や各担当者から送られてきた同じ形式の Excel ファイルを、1つのファイルにまとめる作業です。ファイルが毎月数十個あると、開いてコピーして閉じる作業だけで1時間以上かかることもあります。

Dir 関数を使うと、フォルダ内のファイル名を1つずつ取り出せます。それぞれを開いてデータを転記し、閉じる、という流れを自動化します。

Dir 関数の基本

Dir に「フォルダパス＋ワイルドカード」を渡すと最初のファイル名が、引数なしで呼ぶと次のファイル名が返ります。ファイルがなくなると空文字列になるので、それまでループします。

Dim f As String
f = Dir("C:\data\報告\*.xlsx")
Do While f <> ""
    Debug.Print f
    f = Dir()
Loop

コード

マクロのあるブックと同じ場所の「報告」フォルダ内のファイルから、「報告」シートのデータを集めます。読み取り専用で開き、保存せずに閉じるので、元のファイルを変更してしまう心配がありません。

Sub MergeFolder()
    Dim folder As String, f As String
    Dim src As Workbook, outWs As Worksheet
    Dim lastRow As Long, outRow As Long, data As Variant, n As Long

    folder = ThisWorkbook.Path & "\報告\"
    Set outWs = ThisWorkbook.Worksheets("集約")
    outWs.Range("A2:Z" & outWs.Rows.Count).ClearContents
    outRow = 2

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    f = Dir(folder & "*.xls*")
    Do While f <> ""
        If Left(f, 2) <> "~quot; Then                    ' 開いている最中の一時ファイルを除く
            Set src = Workbooks.Open(folder & f, ReadOnly:=True)
            With src.Worksheets("報告")
                lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
                If lastRow >= 2 Then
                    data = .Range("A2:F" & lastRow).Value
                    outWs.Cells(outRow, "A").Resize(lastRow - 1, 1).Value = f
                    outWs.Cells(outRow, "B").Resize(lastRow - 1, 6).Value = data
                    outRow = outRow + lastRow - 1
                End If
            End With
            src.Close SaveChanges:=False
            n = n + 1
        End If
        f = Dir()
    Loop
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    MsgBox n & " ファイル、" & outRow - 2 & " 件を集約しました"
End Sub

コードのポイント

ファイル名が「~$」で始まるものは、誰かがそのファイルを開いているときに作られる一時ファイルです。これを開こうとするとエラーになるため除外しています。

A列には元のファイル名を書き込んでいます。集約後に数字がおかしいデータを見つけたとき、どのファイルから来たかをすぐたどれるので、実務では必ず入れておくことをおすすめします。

エラーに備える

シート名が違うファイルが1つでも混ざると、「インデックスが有効範囲にありません」（実行時エラー 9）で止まり、開いたファイルが閉じられずに残ります。本番では、シートの存在を確認してから処理するか、エラー処理を入れて、エラーが起きたファイル名を記録して次へ進むようにすると安心です。

• 一時ファイル（~$）を除外する
• 読み取り専用で開き、保存せずに閉じる
• 元ファイル名を記録しておく
• シート名の違うファイルに備える

ほかの記事