[VBA] How to Extract Column D from Multiple Excel Files into a List (Batch Processing)

Background

In my daily work, I often encounter situations where I need to summarize common items contained in multiple Excel files. Specifically, I wanted to collect data from Column D (the 4th column) of every file and combine it into a single sheet.

In this article, I will share the VBA code I learned to automate this process.

Goal

• Target all Excel files (extensions: .xls, .xlsx, .xlsm) in a specific folder.
• Get Column D from the first sheet of each file.
• Compile them into a single list in one Excel sheet.

VBA Code

Here is the VBA code to achieve this.

Sub GetExcelFiles()

    Dim FolderPath As String
    Dim FileName As String
    Dim OutputWorkbook As Workbook
    Dim OutputWorksheet As Worksheet
    Dim SourceWorkbook As Workbook
    Dim SourceWorksheet As Worksheet
    Dim NextOutputRow As Long
    Dim LastRow As Long

    ' Specify the target folder path (Don't forget the last "\")
    FolderPath = "C:\Path\to\your\folder\"

    ' Set the output workbook and sheet
    Set OutputWorkbook = ThisWorkbook
    Set OutputWorksheet = OutputWorkbook.Sheets("Sheet1")

    ' Start writing from row 1
    NextOutputRow = 1

    ' Find all Excel files in the folder
    FileName = Dir(FolderPath & "*.xls*")

    Do While FileName <> ""
        ' Open the target Excel file
        Set SourceWorkbook = Workbooks.Open(FolderPath & FileName)
        Set SourceWorksheet = SourceWorkbook.Sheets(1)

        ' Get the last row of Column D
        LastRow = SourceWorksheet.Cells(SourceWorksheet.Rows.Count, "D").End(xlUp).Row

        ' Copy Column D data to the output worksheet
        SourceWorksheet.Range("D1:D" & LastRow).Copy OutputWorksheet.Range("A" & NextOutputRow)

        ' Update the next row number for writing
        NextOutputRow = OutputWorksheet.Cells(OutputWorksheet.Rows.Count, "A").End(xlUp).Row + 1

        ' Close the source file without saving
        SourceWorkbook.Close SaveChanges:=False

        ' Move to the next file
        FileName = Dir
    Loop

    MsgBox "Aggregation of Column D is complete.", vbInformation

End Sub

Important Notes Before Running

1. Folder Path: Please change FolderPath to your actual folder path (e.g., C:\Users\user\Documents\ExcelData\). Ensure you include the backslash \ at the end.
2. Output Sheet: The output destination is set to “Sheet1”. Please make sure this sheet exists in your workbook.
3. Empty Cells: If Column D in the source file has empty rows, empty cells may be copied.

Process Flow

1. Scan Folder: Scans the specified folder.
2. Open Files: Opens files with extensions .xls, .xlsx, or .xlsm one by one.
3. Read Data: Reads Column D from the first sheet of the opened file.
4. Copy & Paste: Pastes the data sequentially into “Sheet1” of the master workbook.
5. Complete: The process finishes after checking all files in the folder.

Summary

By using this VBA code, you can completely automate the manual task of opening files and copying data. This script is especially useful for routine monthly or weekly aggregation tasks where you need to compile data from many sources.