如何用单个带宏工作簿批量处理文件夹中所有原始数据文件？

Hey there! Let's expand your existing Excel macro workbook to handle batch processing of multiple raw data files in a folder. Based on your setup (with "Data Entry" and "Output" sheets), here's a practical, step-by-step solution:

We’ll build a new macro that:

• Lets you pick the target folder with your raw data files
• Loops through every valid data file in that folder
• Imports each file’s data into your "Data Entry" sheet (starting at A2, overwriting previous entries)
• Runs your existing analysis/integration macro
• Appends the results from "Output" to a running total in the same sheet (so you don’t lose previous batch results)
• Closes each raw data file without saving changes

1. Add the Batch Processing Macro #

Open your workbook, press Alt + F11 to open the VBA Editor, insert a new module, and paste this code (adjust the details to match your existing macro names and file types):

Sub BatchProcessFiles()
    Dim targetFolder As String
    Dim rawFile As String
    Dim rawWorkbook As Workbook
    Dim dataEntrySheet As Worksheet
    Dim outputSheet As Worksheet
    Dim lastOutputRow As Long
    Dim lastRawRow As Long
    
    ' Turn off screen updates to speed things up and avoid flickering
    Application.ScreenUpdating = False
    
    ' Set references to your existing sheets
    Set dataEntrySheet = ThisWorkbook.Worksheets("Data Entry")
    Set outputSheet = ThisWorkbook.Worksheets("Output")
    
    ' Let user select the folder with raw data files
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select Folder with Raw Data Files"
        If .Show = -1 Then
            targetFolder = .SelectedItems(1) & "\"
        Else
            ' User canceled, exit macro
            MsgBox "Folder selection canceled. Exiting batch process."
            Application.ScreenUpdating = True
            Exit Sub
        End If
    End With
    
    ' Get first file in the folder (adjust file extension to match your raw data: .xlsx, .csv, etc.)
    rawFile = Dir(targetFolder & "*.xlsx")
    
    ' Keep track if we've already copied the Output header (only do this once)
    Dim headerCopied As Boolean
    headerCopied = False
    
    ' Loop through all files in the folder
    Do While rawFile <> ""
        ' Open the raw data workbook
        Set rawWorkbook = Workbooks.Open(targetFolder & rawFile)
        
        ' Clear existing data in Data Entry (keep row 1 with macro buttons intact)
        dataEntrySheet.Range("A2:" & dataEntrySheet.Cells(dataEntrySheet.Rows.Count, "A").End(xlUp).Address).EntireRow.Delete
        
        ' Copy data from raw workbook (assuming raw data starts at A1; adjust if needed)
        lastRawRow = rawWorkbook.Sheets(1).Cells(rawWorkbook.Sheets(1).Rows.Count, "A").End(xlUp).Row
        rawWorkbook.Sheets(1).Range("A1:A" & lastRawRow).EntireRow.Copy dataEntrySheet.Range("A2")
        
        ' Run your existing analysis/integration macro (replace "YourExistingMacroName" with your actual macro name)
        Call YourExistingMacroName
        
        ' Append results from Output sheet to the running total
        lastOutputRow = outputSheet.Cells(outputSheet.Rows.Count, "A").End(xlUp).Row
        
        If Not headerCopied Then
            ' Copy header and data first time
            outputSheet.Range("A1:" & outputSheet.Cells(lastOutputRow, outputSheet.Columns.Count).End(xlToLeft).Address).Copy _
                ThisWorkbook.Worksheets("Output").Range("A1")
            headerCopied = True
        Else
            ' Copy only data (skip header) for subsequent files
            outputSheet.Range("A2:" & outputSheet.Cells(lastOutputRow, outputSheet.Columns.Count).End(xlToLeft).Address).Copy _
                ThisWorkbook.Worksheets("Output").Cells(lastOutputRow + 1, "A")
        End If
        
        ' Close raw workbook without saving changes
        rawWorkbook.Close SaveChanges:=False
        
        ' Get next file in folder
        rawFile = Dir
    Loop
    
    ' Turn screen updates back on
    Application.ScreenUpdating = True
    
    MsgBox "Batch processing complete! Results are in the Output sheet."
End Sub

2. Adjust the Macro to Match Your Setup #

• Replace YourExistingMacroName with the actual name of the macro that processes "Data Entry" data into "Output".
• If your raw data files use a different extension (like .csv or .xls), change *.xlsx in the Dir line to match.
• If your raw data starts at a different cell (not A1), adjust the copy range in the rawWorkbook.Sheets(1).Range(...) line.

3. Add a Batch Button to Your Data Entry Sheet #

Go back to your "Data Entry" sheet, add a new button (Developer tab > Insert > Button (Form Control)), and assign the BatchProcessFiles macro to it. Now you can click this button to start batch processing!

• Error Handling: For robustness, you might want to add error handling (like On Error Resume Next or On Error GoTo) to handle corrupted files or unexpected data formats.
• Screen Updating: We turned off screen updates to make the process faster—don’t forget to turn it back on even if the macro hits an error (you can add this in an error handler).
• Data Clearing: The macro clears old data from "Data Entry" before importing each new file—make sure this doesn’t conflict with any static data you have in that sheet.

内容的提问来源于stack exchange，提问作者McClueless12