【VBA】特定のフォルダ内の全Excelファイルを外部参照で抽出する方法

日々の業務で複数のExcelファイルから必要な情報を一つのワークシートにまとめたい場合、手作業では時間がかかります。この記事では、特定のフォルダ内の全てのExcelファイルを外部参照を使って一括抽出し、効率化を図るVBAマクロを紹介します。

サンプルコード

VBA

Option Explicit
Sub ExtractDataFromFolder()
    Dim fso As Object, folder As Object, file As Object
    Dim wb As Workbook, ws As Worksheet
    Dim lastRow As Long, i As Long
    
    ' 外部参照を設定するワークシートを作成または選択
    Set ws = ThisWorkbook.Sheets.Add
    ws.Name = "抽出結果"
    
    ' シートの更新を停止し、処理速度を向上させる
    Application.ScreenUpdating = False
    
    ' フォルダオブジェクトを作成
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set folder = fso.GetFolder("C:\指定フォルダパス")
    
    ' 各ファイルに対して処理を行う
    For Each file In folder.Files
        If Right(file.Name, 4) = ".xls" Or Right(file.Name, 5) = ".xlsx" Then
            Set wb = Workbooks.Open(file.Path)
                lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1
                ' データを抽出する範囲を指定（例：A2から始まるデータ列）
                With wb.Sheets(1) ' シート名や番号を適宜変更
                    ws.Cells(lastRow, "A").Value = .Range("A2").Value ' A列の値をコピー
                End With
            wb.Close SaveChanges:=False
        End If
    Next file
    
    Application.ScreenUpdating = True
End Sub

よくある質問

Q 元に戻せますか？

VBAの実行結果は「元に戻す」が効きません。必ずバックアップを取ってから実行してください。

Q エラーが出たら？

シート名や列番号が正しいか確認してください。