トップ > VBA・VBS > Excel VBA > フォルダ内の複数ブックを開いて1つに統合する

フォルダ内の複数ブックを開いて1つに統合する

Excel VBA(支店・担当者から届くファイルをまとめる)

支店や担当者から、同じ形式のExcelファイルがフォルダに集まってくる。月末の統合は、ファイルを開いて、コピペして、閉じて、の繰り返しです。このレシピでは、フォルダを指定するだけで、中のExcelファイルを全部開いて1枚に統合するマクロを作ります。統合元のファイルは「読み取り専用」で開くので、元のファイルが書き換わることは絶対にありません。

✅ これができます:フォルダ内の全Excelファイルを、ボタン1つで1枚に統合できるようになります

完成形(実行前 → 実行後)

実行前
実行前
実行後
実行後

実行前(左)はフォルダに集まった各支店のファイル。実行すると(右)、全ファイルのデータが1枚に統合されます。元のファイルは読み取り専用で開くため変更されません

これをコピペすれば動きます

Option Explicit
 
' ===== 設定エリア:変えるのはここだけ =====
' ① 統合したいファイルが入っているフォルダ
Const FOLDER_PATH As String = "C:\仕事\支店データ"
' ② 統合先のシート名(このブックの中)
Const OUTPUT_SHEET As String = "統合"
' ③ 読み取るシート名("" のまま=各ブックの先頭シート)
Const TARGET_SHEET As String = ""
' ④ 各ファイルの見出し行の数
Const HEADER_ROWS As Long = 1
' ===== ここから下は変えなくてOK =====
 
Sub ブックを統合する()
    Dim wsOut As Worksheet, wsSrc As Worksheet
    Dim fso As Object, folder As Object, file As Object
    Dim wb As Workbook
    Dim outRow As Long, lastRow As Long, lastCol As Long
    Dim headerDone As Boolean, fileCnt As Long
 
    Set fso = CreateObject("Scripting.FileSystemObject")
    If Not fso.FolderExists(FOLDER_PATH) Then
        MsgBox "フォルダが見つかりません:" & FOLDER_PATH, vbExclamation, "ブック統合"
        Exit Sub
    End If
    Set folder = fso.GetFolder(FOLDER_PATH)
    Set wsOut = シートを用意する(OUTPUT_SHEET)
    wsOut.Cells.Clear
    outRow = 1
    headerDone = False
    fileCnt = 0
 
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    For Each file In folder.Files
        If Excelファイルか(fso, file.Name) And file.Path <> ThisWorkbook.FullName Then
            Set wb = Nothing
            On Error Resume Next
            Set wb = Workbooks.Open(file.Path, ReadOnly:=True)
            On Error GoTo 0
            If Not wb Is Nothing Then
                Set wsSrc = 読み取りシートを取る(wb)
                If Not wsSrc Is Nothing Then
                    lastRow = wsSrc.Cells(wsSrc.Rows.Count, 1).End(xlUp).Row
                    lastCol = wsSrc.Cells(HEADER_ROWS, wsSrc.Columns.Count).End(xlToLeft).Column
                    If lastRow > HEADER_ROWS Then
                        If Not headerDone Then
                            wsSrc.Range(wsSrc.Cells(1, 1), wsSrc.Cells(HEADER_ROWS, lastCol)).Copy wsOut.Cells(outRow, 1)
                            outRow = outRow + HEADER_ROWS
                            headerDone = True
                        End If
                        wsSrc.Range(wsSrc.Cells(HEADER_ROWS + 1, 1), wsSrc.Cells(lastRow, lastCol)).Copy wsOut.Cells(outRow, 1)
                        outRow = outRow + (lastRow - HEADER_ROWS)
                        fileCnt = fileCnt + 1
                    End If
                End If
                wb.Close SaveChanges:=False
            End If
        End If
    Next file
    Application.EnableEvents = True
    Application.ScreenUpdating = True
 
    wsOut.Activate
    MsgBox fileCnt & " 件のファイルを「" & OUTPUT_SHEET & "」に統合しました。", vbInformation, "ブック統合"
End Sub
 
Private Function Excelファイルか(ByVal fso As Object, ByVal fileName As String) As Boolean
    Dim ext As String
    ext = LCase(fso.GetExtensionName(fileName))
    Excelファイルか = (ext = "xlsx" Or ext = "xlsm" Or ext = "xls")
End Function
 
Private Function 読み取りシートを取る(ByVal wb As Workbook) As Worksheet
    Dim ws As Worksheet
    Set 読み取りシートを取る = Nothing
    If TARGET_SHEET = "" Then
        Set 読み取りシートを取る = wb.Worksheets(1)
        Exit Function
    End If
    For Each ws In wb.Worksheets
        If ws.Name = TARGET_SHEET Then
            Set 読み取りシートを取る = ws
            Exit Function
        End If
    Next ws
End Function
 
Private Function シートを用意する(ByVal sheetName As String) As Worksheet
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name = sheetName Then
            Set シートを用意する = ws
            Exit Function
        End If
    Next ws
    Set ws = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
    ws.Name = sheetName
    Set シートを用意する = ws
End Function

コードの黄色い部分が「設定エリア」です。変えるのは、原則ここだけです。

変えるのはここだけ

変える場所を示した図
変えるのは設定エリアの4か所だけ。番号は下の表と対応しています
番号変える場所意味書き方・例
1FOLDER_PATH統合したいファイルが入っているフォルダ自分のPCの場所に書き換えます。エクスプローラーのアドレスバーの文字列がそのまま使えます
2OUTPUT_SHEET統合先のシート名(このブックの中)無ければ自動で作られます。このブック自身が①のフォルダにある場合は、自動で対象から外れます
3TARGET_SHEET各ファイルから読み取るシート名"" のままなら先頭シート。ファイルごとにシート名が決まっている場合は、その名前を書きます(無いファイルは飛ばされます)
4HEADER_ROWS各ファイルの見出し行の数見出しが1行なら 1。全ファイルで形式が揃っている前提です

組み込み手順

  1. 統合したいファイルを、1つのフォルダに集めておきます(形式が揃っていることが前提です)
  2. 統合先のブック(このマクロを入れるブック)を、①のフォルダとは別の場所に用意します
  3. Alt+F11 でVBAエディタを開き、「挿入」→「標準モジュール」を選びます
  4. このページのコードを貼り付け、設定エリア①〜④を書き換えます
  5. ファイルを .xlsm で保存し、マクロを実行します
  6. 「○件のファイルを統合しました」と出て、統合シートに全データが積み上がっていれば成功です

⚠ VBAエディタの開き方:Alt+F11 で開きます。「開発」タブが出ていない場合は、ファイル→オプション→リボンのユーザー設定で「開発」にチェックを入れると出ます。

⚠ 保存形式:マクロは「Excel マクロ有効ブック(.xlsm)」で保存しないと、次に開いたときに消えています。いつもの .xlsx で保存すると、マクロだけ取り除かれます。

⚠ セキュリティ警告バー:開いたときに黄色いバーが出たら「コンテンツの有効化」を押してください。押すまではマクロは動きません。

⚠ ファイルのブロック解除:ネットから入手したファイルは、開く前に「右クリック→プロパティ→『許可する』にチェック」が先です。これをしないと、有効化のボタン自体が出ないことがあります。

⚠ 会社の設定で無効化されている場合:警告バーも出ずに無反応なときは、会社の設定でマクロが止められている可能性が高いです。設定を突破しようとせず、情シスに「業務でマクロを使いたい」と相談するのが正解です。

動かし方

実行のたびに統合シートはまっさらな状態から作り直されます。新しいファイルがフォルダに増えたら、もう一度実行するだけで最新化できます。統合元のファイルは読み取り専用で開くため、保存し直されることはありません。

カスタマイズ例

ファイル名の列を足して、出典を残したい

統合した行がどのファイル由来かは、この作りでは残りません。出典が必要な現場では、統合前に各ファイル側でファイル名列を持たせるか、出典列つきの発展版にするのがおすすめです。

サブフォルダの中まで対象にしたい

このレシピは指定フォルダの直下だけが対象です。月別サブフォルダ運用の場合は、①を月ごとに変えて実行するのが簡単で確実です。

統合した表を集計まで一気に

統合シートを「集計ボタン」レシピのデータシートに指定すれば、ファイル統合→項目別集計まで流れで処理できます。

つまずきどころ

関連レシピ

次はこれ:CSVを一括で取り込む/シートをCSVで一括書き出す
ファイル統合の次は、システムから出てくるCSVの取り込みを自動化しましょう

よくある質問

Q. 統合元のファイルが書き換わることはある?

A. ありません。読み取り専用で開き、保存せずに閉じる作りです。統合先は「このマクロを入れたブック」のシートだけです。

Q. パスワードが掛かったファイルが混ざっていたら?

A. 開けないファイルは自動で飛ばされる作りにしています。統合された件数が思ったより少ないときは、パスワード付きファイルや壊れたファイルが混ざっていないか確認してください。パスワードを聞くダイアログが出た場合は、キャンセルすればそのファイルだけ飛ばされます。

Q. CSVファイルも統合できる?

A. このレシピの対象外です。CSVの一括取り込みは「CSVを一括で取り込む/書き出す」のレシピで扱います。

PR(広告)

もっと体系的に学びたい人へ
自動化の幅を広げるなら、講座で体系化するのが近道です。

Udemy:Excelショートカット講座を見る