VBAでフォルダ内のExcelファイルを一括処理する｜Dir関数で順に開いて集める

フォルダ内のファイルを一括処理したい場面

支店ごとの売上報告、担当者ごとの週報など、同じ形式の Excel ファイルが何十個も届き、1 つずつ開いて数字を書き写している、という作業はよくあります。VBA でフォルダの中のファイルを順に開くようにすると、ファイルが何個あってもマクロを 1 回動かすだけで終わります。

この記事では、次の 2 つのマクロを作ります。

• 集める: フォルダ内の全ファイルの決まったセルの値を、1 枚のシートに一覧にする
• 直す: フォルダ内の全ファイルの決まったセルを書き換えて保存する

VBA のコードを書くのが初めての場合は、先に Excelマクロの作り方 で VBE の開き方と保存の形式を確かめてください。

Dir関数でファイル名を順に取る仕組み

Microsoft の公式リファレンスによると、Dir 関数は次のように動きます。

Windows では、* (任意の文字の並び) と ? (任意の 1 文字) のワイルドカードで対象を絞れます。返るのはファイル名だけで、フォルダのパスは付かない点に注意します。開くときは、フォルダのパスとつなげて使います。

準備するフォルダとシート

マクロを書くブック (.xlsm) と同じ場所に「集計元」フォルダを作り、その中に集めたいファイルを入れます。

「集計」シートの 1 行目には、A1 に「ファイル名」、B1 に「売上合計」と見出しを入れておきます。

全ファイルの値を一覧に集めるマクロ

集計.xlsm の標準モジュールに、次のコードを貼り付けて実行します。

Sub CollectFromFolder()
    Dim folderPath As String
    Dim fileName As String
    Dim sourceBook As Workbook
    Dim summarySheet As Worksheet
    Dim outputRow As Long

    folderPath = ThisWorkbook.Path & "\集計元\"
    Set summarySheet = ThisWorkbook.Worksheets("集計")
    outputRow = 2

    Application.ScreenUpdating = False

    fileName = Dir(folderPath & "*.xlsx")
    Do While fileName <> ""
        Set sourceBook = Workbooks.Open(Filename:=folderPath & fileName, ReadOnly:=True)
        summarySheet.Cells(outputRow, "A").Value = fileName
        summarySheet.Cells(outputRow, "B").Value = sourceBook.Worksheets(1).Range("B2").Value
        sourceBook.Close SaveChanges:=False
        outputRow = outputRow + 1
        fileName = Dir()
    Loop

    Application.ScreenUpdating = True
    MsgBox outputRow - 2 & " 件のファイルを読み込みました"
End Sub

コードの流れ

1. フォルダの場所を決める

   ThisWorkbook.Path は、このマクロを書いたブックが保存されているフォルダのパスです。そこに \集計元\ をつなげて、読み込むフォルダを決めています。

2. 最初のファイル名を取る

   Dir(folderPath & "*.xlsx") で、フォルダの中の .xlsx ファイルの最初の 1 つの名前を取ります。

3. 開いて値を写し、閉じる

   Workbooks.Open でファイルを開き、1 枚目のシートの B2 の値を「集計」シートに書きます。読むだけなので読み取り専用で開き、保存せずに閉じます。

4. 次のファイル名を取る

   fileName = Dir() で次のファイル名を取ります。ファイルがなくなると "" が返り、Do While fileName <> "" の条件が満たされなくなってループが終わります。

Workbooks.Open の公式リファレンスのとおり、ReadOnly に True を指定すると読み取り専用で開き、戻り値として開いたブック (Workbook) が返ります。Set sourceBook = ... で受け取っておくと、そのブックを確実に指定して読み書きできます。

全ファイルを書き換えて保存するマクロ

今度は、全ファイルの 1 枚目のシートの B1 に「2026年10月分」と入れて保存する例です。

Sub UpdateAllFiles()
    Dim folderPath As String
    Dim fileName As String
    Dim targetBook As Workbook
    Dim updatedCount As Long

    folderPath = ThisWorkbook.Path & "\集計元\"
    updatedCount = 0

    Application.ScreenUpdating = False

    fileName = Dir(folderPath & "*.xlsx")
    Do While fileName <> ""
        Set targetBook = Workbooks.Open(Filename:=folderPath & fileName)
        targetBook.Worksheets(1).Range("B1").Value = "2026年10月分"
        targetBook.Close SaveChanges:=True
        updatedCount = updatedCount + 1
        fileName = Dir()
    Loop

    Application.ScreenUpdating = True
    MsgBox updatedCount & " 件のファイルを更新しました"
End Sub

Workbook.Close の公式リファレンスのとおり、SaveChanges に True を指定すると変更を保存して閉じ、False なら保存せずに閉じます。

画面の更新を止めて速くする

2 つのマクロとも、最初に Application.ScreenUpdating = False を入れています。公式リファレンスによると、画面の更新を止めると処理の様子は見えなくなりますが、マクロの実行は速くなります。マクロが終わったら True に戻すよう案内されているので、最後に戻しています。

うまく動かないときの確認点

*.xls* のようにワイルドカードを書くと、.xls・.xlsx・.xlsm をまとめて対象にできます。ただしマクロを書いたブックと同じフォルダを対象にすると、自分自身も開こうとしてしまうので、この記事のように対象のファイルは別のフォルダに分けておきます。

自分で作るのが難しいときは

フォルダ内のファイルの一括処理は、手作業の時間を大きく減らせる自動化です。ただ、届くファイルの形式が支店ごとに少しずつ違う、途中で開けないファイルがあると止まってしまう、サブフォルダや共有フォルダもまとめて処理したい、といった条件が加わると、作り込みが必要になります。

毎月の集計の流れごと自動にしたいときや、作ったマクロが一部のファイルで止まって困っているときは、シートナビの相談フォームからお気軽にご相談ください。

よくある質問