【Excel VBA】フォルダ内の全ブックから特定セルの値を一覧にするマクロ

フォルダに保存した月次報告や支店別データなど、複数の Excel ブックから同じ位置のセルを 1 つずつ開いて転記していませんか。VBA マクロを使えば、フォルダ内の全ブックから指定セルの値を自動で一覧にできます。

この記事では、コピーしてすぐ使える完成コードと、VBE（Visual Basic Editor）での実行手順を解説します。取得セルの変更や複数セル対応などのカスタマイズ例と、動かないときの確認ポイントも紹介します。

練習用のサンプルファイルもダウンロードできます。

zip を展開すると、マクロ入りブック（collector.xlsm）と 3 つのデータブックが入った data フォルダが入っています。そのまま実行してマクロの動作を確認できます。

フォルダ内の全ブックからセルの値を集めるマクロ（完成コード）

以下のコードをコピーして、次のセクションの手順で実行すると、指定フォルダ内のすべての .xlsx ファイルから特定セルの値を読み取り、マクロブックの「一覧」シートに書き出します。元のファイルは一切変更しません。

Sub CollectCellValues()
    Dim folderPath As String
    Dim fileName As String
    Dim wb As Workbook
    Dim wsOut As Worksheet
    Dim outRow As Long

    folderPath = ThisWorkbook.Path & "data"
    Set wsOut = ThisWorkbook.Worksheets("一覧")
    outRow = 2

    fileName = Dir(folderPath & "*.xlsx")
    Do While fileName <> ""
        If Left(fileName, 2) <> "~$" Then
            Set wb = Workbooks.Open(folderPath & fileName, _
                                    ReadOnly:=True, UpdateLinks:=0)
            wsOut.Cells(outRow, 1).Value = fileName
            wsOut.Cells(outRow, 2).Value = wb.Worksheets(1).Range("B2").Value
            wb.Close SaveChanges:=False
            outRow = outRow + 1
        End If
        fileName = Dir()
    Loop

    MsgBox outRow - 2 & " 件のブックから値を取得しました"
End Sub

コードの中で書き換えが必要な箇所は 3 か所です。

• "data" ― 対象ブックが入っているフォルダ名
• "一覧" ― マクロブック内の出力先シート名
• Range("B2") ― 取得したいセル番地

次のセクションで、フォルダ構成の作り方とマクロの実行方法を説明します。

マクロの使い方（フォルダ構成と実行手順）

フォルダ構成を作る

マクロブック（コードを貼り付けるファイル）と、値を集める対象ブックは別のフォルダに分けるのがおすすめです。同じフォルダに混在させると管理しにくく、将来 Dir のパターンを *.xls* に広げたときにマクロブック自身が処理対象に紛れ込むリスクもあります。

おすすめのフォルダ構成は次のとおりです。

月次集計
├── collector.xlsm    ← マクロを書くブック
└── data
    ├── report_tokyo.xlsx
    ├── report_osaka.xlsx
    └── report_nagoya.xlsx

コード内の ThisWorkbook.Path & "data" は、マクロブックと同じ階層にある data フォルダを指します。フォルダ名を変えたいときは "data" の部分を書き換えてください。

マクロブックのシートを準備する

マクロブックに「一覧」という名前のシートを作り、1 行目にヘッダーを入れておきます。マクロは 2 行目（outRow = 2）から書き込みます。

シート名が「一覧」でなくてもかまいませんが、コード内の Worksheets("一覧") を実際のシート名に合わせて書き換えてください。

VBE を起動してコードを貼り付ける

1. Alt+F11 を押して VBE（Visual Basic Editor）を開く
2. メニューバーの「挿入」→「標準モジュール」をクリック
3. 右側に開いたコードウィンドウに、完成コードを貼り付ける
4. "data"、"一覧"、Range("B2") を自分の環境に合わせて書き換える

マクロを実行する

1. コードウィンドウ内にカーソルを置いた状態で F5 を押す
2. 処理が終わると「○件のブックから値を取得しました」とメッセージが表示される
3. Alt+F11 でシートに戻ると、一覧が書き込まれている

保存形式の注意: マクロ入りブックは .xlsm 形式で保存します。通常の .xlsx で保存するとマクロが消えるため、保存時に「マクロ有効ブック (*.xlsm)」を選んでください。

マクロで書き込んだデータは Ctrl+Z では元に戻せません。やり直したいときは、一覧シートの 2 行目以降を手動で削除してからマクロを再実行してください。

コードの解説

完成コードを貼り付けて動かすだけなら前のセクションまでで十分ですが、自分で書き換えるときにはしくみの理解が役立ちます。

Dir 関数でフォルダ内のファイルを順に取得する

Dir 関数は、指定パターンに一致するファイル名を 1 つ返します。

1. Dir(folderPath & "*.xlsx") ― 最初のファイル名を返す
2. Dir()（引数なし）― 次のファイル名を返す。もうなければ空文字 "" を返す

この 2 ステップを Do While ループで繰り返すことで、フォルダ内の全 .xlsx ファイルを順番に処理します。

Dir は内部に「次に返すファイル」の情報を保持しています。そのため、ループの中で別の Dir を呼ぶと、元のループの状態が壊れます。サブフォルダの中も処理したいケースでは Dir のネストは使えないため、FileSystemObject など別のしくみが必要です（この記事では扱いません）。

~$ で始まるファイルをスキップする理由

Excel でブックを開いている間、同じフォルダに ~$ファイル名.xlsx という一時ロックファイルが作られます。このファイルも Dir("*.xlsx") にマッチするため、そのまま開こうとするとエラーになります。

完成コードの If Left(fileName, 2) <> "~$" は、このロックファイルを処理対象から除外するためのガードです。

Workbooks.Open のオプション

完成コードでは ReadOnly:=True と UpdateLinks:=0 を指定しています。

閉じるときの wb.Close SaveChanges:=False は「保存しないで閉じる」の意味です。読み取り専用で開いているので変更はありませんが、念のため明示しています。

Worksheets(1) を使う理由 ― Sheets(1) との違い

完成コードでは wb.Worksheets(1) で各ブックの先頭ワークシートを取得しています。似たプロパティに Sheets がありますが、次の違いがあります。

通常のブックであれば Sheets(1) でも動きますが、先頭にグラフシートがあるブックでは Sheets(1) がグラフシートを返すため、.Range("B2") の呼び出しでエラーになります。Worksheets(1) ならグラフシートを飛ばしてワークシートだけを対象にするため、こちらを使う方が確実です。

また、シート名で指定する Sheets("Sheet1") や Worksheets("Sheet1") は、シート名が変更されているブックでエラーになります。全ブックの先頭ワークシートにある同じセルを読み取る前提であれば、Worksheets(1) が最も安全な書き方です。ブックによって値が 2 枚目以降のシートにある場合は、シート名やインデックスを合わせて変更してください。

.Value が返す値の種類

取得したセルの内容によって、.Value が返す値の型は異なります。

実務で多いのは文字列と数値です。数式が入ったセルでも .Value は計算結果を返すため、通常はそのまま使えます。

カスタマイズ例

完成コードの一部を書き換えるだけで、さまざまな業務に対応できます。

取得するセルを変更する

たとえば A1 セルの値を集めたい場合は、Range("B2") を Range("A1") に変えます。

wsOut.Cells(outRow, 2).Value = wb.Worksheets(1).Range("A1").Value

複数セルの値をまとめて取得する

1 つのブックから複数のセルを読み取りたい場合は、書き込む列を増やします。

wsOut.Cells(outRow, 1).Value = fileName
wsOut.Cells(outRow, 2).Value = wb.Worksheets(1).Range("A1").Value  '会社名
wsOut.Cells(outRow, 3).Value = wb.Worksheets(1).Range("B5").Value  '売上
wsOut.Cells(outRow, 4).Value = wb.Worksheets(1).Range("C10").Value '利益

ヘッダー行もそれに合わせて A1 に「ファイル名」、B1 に「会社名」、C1 に「売上」、D1 に「利益」と入れておきます。

特定のシート名を指定する

全ブックに「集計」という名前のシートがある場合は、シート名で指定できます。

wsOut.Cells(outRow, 2).Value = wb.Worksheets("集計").Range("B2").Value

ただし、「集計」シートが存在しないブックが 1 つでもあるとエラーで止まります。シート名が統一されていない場合は Worksheets(1) を使ってください。

1 冊だけ壊れていても全体を止めないエラーハンドリング

対象フォルダにパスワード付きファイルや破損したファイルが混ざっていると、マクロが途中で止まります。次のように On Error Resume Next / On Error GoTo 0 で囲むと、問題のあるブックをスキップして残りの処理を続けられます。

fileName = Dir(folderPath & "*.xlsx")
Do While fileName <> ""
    If Left(fileName, 2) <> "~$" Then
        On Error Resume Next
        Set wb = Workbooks.Open(folderPath & fileName, _
                                ReadOnly:=True, UpdateLinks:=0)
        If Err.Number = 0 Then
            wsOut.Cells(outRow, 1).Value = fileName
            wsOut.Cells(outRow, 2).Value = wb.Worksheets(1).Range("B2").Value
            wb.Close SaveChanges:=False
            outRow = outRow + 1
        Else
            wsOut.Cells(outRow, 1).Value = fileName
            wsOut.Cells(outRow, 2).Value = "（取得失敗）"
            outRow = outRow + 1
            Err.Clear
        End If
        On Error GoTo 0
    End If
    fileName = Dir()
Loop

取得に失敗したブックは「（取得失敗）」と記録されるため、あとから手動で確認できます。

.xlsx 以外のファイルも対象にする

Dir のパターンを変えると、対象ファイルの範囲を広げられます。

fileName = Dir(folderPath & "*.xls*")

*.xls* にすると .xlsx だけでなく .xlsm（マクロ有効ブック）や .xls（旧形式）も対象になります。.xlsm のブックにはマクロが含まれている可能性があるため、対象が本当に広がってよいか確認してから使ってください。限定できる場合は *.xlsx のままにしておく方が安全です。

うまく動かないときの確認ポイント

マクロを実行してエラーが出たり、結果が期待どおりにならない場合は、次の表を確認してください。

Dir のネストに注意

マクロを改造するときに注意したい制約があります。Dir 関数はプログラム全体で 1 つの状態しか保持できないため、ループの中で別の Dir を呼ぶと、外側のループが壊れます。

たとえば「サブフォルダのファイルも処理したい」と思って、ループ内でサブフォルダに対して Dir を呼ぶと、元のファイル走査が途中で終わってしまいます。サブフォルダを含む再帰的な処理が必要な場合は、FileSystemObject を使う方法に切り替える必要があります。

まとめ

• Dir ループ + Workbooks.Open で、フォルダ内の全ブックから任意のセル値を一括取得できる
• ReadOnly:=True を付けると、元のファイルを誤って上書きする心配がない
• シートの指定には Worksheets(1) を使う。Sheets(1) はグラフシートも含むため、意図しないエラーの原因になる
• 取得セルの変更は Range("B2") の部分を書き換えるだけ。複数セルへの拡張も簡単
• 動かないときは、パス末尾の とシートの指定方法をまず確認する