VBA & Macro Problems

How to Process Multiple Open Excel Workbooks Automatically with VBA

Amos Gikunda Oct 10, 2026 869 views

Question details

The user needs a VBA macro to automatically find and copy data from up to 50 concurrently open workbooks (named Batch01 to Batch50) into a master file without writing repetitive conditional statements for each file.

How to Process Multiple Open Excel Workbooks Automatically with VBA

Product: Excel
Device & OS: not provided
Scenario: Consolidating data from dozens of open batch files into a single destination workbook (Merge.xlsm) dynamically.
Observed behavior: The current macro relies on repetitive, hard-coded conditional statements for each potential batch file, making the code inefficient and difficult to maintain.

Before you start

Before running the macro, verify that all target batch workbooks and your destination master workbook are currently open in the same Excel instance, and save a backup of your destination file.

Solution 1Recommended

Use an Application.Workbooks Loop to Process Open Files

Iterate through all currently open workbooks dynamically to find matching batch files and consolidate their data.

Instead of writing 50 separate conditional statements for each potential batch file, you can utilize a For Each loop. This method scans all workbooks currently open in the application, checks if their file names match your specific batch pattern, and processes only the correct files while ignoring others like the destination file itself.

Open the VBA Editor

Press ALT + F11 in Excel to launch the Visual Basic for Applications (VBA) Editor.

Declare Workbook Variables

In your module, declare a variable to handle the loop iteration, for example: Dim wb As Workbook.

Set Up the Loop

Create a loop using the syntax: For Each wb In Application.Workbooks.

Apply Pattern Matching

Inside the loop, use an If statement with the Like operator to identify the correct files and exclude the destination file. Example: If wb.Name Like "Batch*.xlsm" And wb.Name <> "Merge.xlsm" Then.

Execute the Copy Procedure

Call your existing copy-and-append code within this If statement block to process the matched workbook.

Close the Loop

End the conditional block with End If and proceed to the next open file by typing Next wb.

Use an Application.Workbooks Loop to Process Open Files

Track Processed Files: You can add a string variable inside the condition to concatenate the names of processed workbooks and display them in a MsgBox at the end of the macro as a handy completion summary.

Efficiently Handle Macros in WPS Office

Run and Edit VBA Macros Seamlessly with WPS Spreadsheet

WPS Office provides robust, built-in support for VBA and macros. You can write, edit, and execute complex scripts—including looping through multiple open workbooks—exactly as you would in Microsoft Excel.

1. Open WPS Spreadsheet: Launch WPS Office and open your .xlsm destination file and batch files.
2. Enable Macros: Navigate to the 'Developer' tab on the ribbon and click on 'Macros' to manage your scripts.
3. Access the VBA Editor: Click 'Visual Basic' or press ALT + F11 to paste and run your Application.Workbooks loop code.

Fully compatible with Microsoft Excel VBA macros and .xlsm/.xltm file formatsFeatures a built-in Visual Basic Editor for writing and debugging codeLightweight application that handles multiple open workbooks smoothly without laggingFree to download and highly cost-effective for daily office productivity

microsoft office alternative - wps office

Frequently Asked Questions

How do I exclude the destination workbook from the VBA loop?

Within your Application.Workbooks loop, add a condition to check the workbook name, such as `If wb.Name <> "Merge.xlsm" Then`. This prevents the macro from attempting to copy data from the master file into itself.

Will this macro process closed workbooks located in a folder?

No, the Application.Workbooks collection only includes workbooks that are actively open in the current application instance. To process closed files, you would need to use the Dir function or FileSystemObject to open them systematically before copying the data.

What happens if no matching batch files are currently open?

If no open files match your naming pattern (e.g., "Batch*.xlsm"), the loop will simply bypass the copy procedure and finish without executing any actions. It is a best practice to include a counter variable that triggers a message box warning you if zero files were processed.

Can I use this loop for workbooks with different file extensions?

Yes, you can modify the pattern matching string in your IF statement. By using a broader wildcard like `If wb.Name Like "Batch*" Then`, the macro will match .xlsx, .csv, or .xls files, as long as they are currently open in Excel.

Excel & Data Problems