How to Create a VBA Macro to Process Excel Files in Matching Subfolders
Question details
The user wants to automate the process of searching through folders and subfolders to find Excel workbooks that match a specific string in their path, applying bulk changes to each, and saving them.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Automating bulk updates to multiple Excel files scattered across various subfolders using VBA.
- Observed behavior
- The user needs a script to recursively search folders, filter by a string keyword, open matching workbooks, execute a formatting or data update macro, and save the results automatically.
Before running any batch-processing macro, ensure you test your VBA code on a single, isolated copy of an Excel file to verify the changes before applying them to hundreds of workbooks in your live directory.
Use a Recursive VBA Macro with Error Handling
The safest and most efficient approach is to separate the folder scanning process from the actual workbook modification process.
By decoupling the search logic from the modification logic, your code becomes much easier to debug. You will use a recursive function using the FileSystemObject to scan subfolders, looking for a specific keyword in the path.
Always include error handling so that if one workbook is corrupted or password-protected, the macro skips it and logs the failure instead of crashing.
Open the VBA Editor by pressing Alt + F11. Click on 'Tools' in the top menu, select 'References', check the box next to 'Microsoft Scripting Runtime', and click 'OK'. This enables the FileSystemObject for advanced folder scanning.
Click 'Insert' > 'Module' and write a separate Sub procedure that takes a Workbook object as a parameter. In this Sub, write the exact formatting or data modifications you want applied, and end it with a save command.
In the same module, create a recursive Sub using FileSystemObject that iterates through a root folder's files and subfolders. Use the 'InStr' function to check if the file's path contains your target string (e.g., 'model') and verify the extension is '.xlsx' or '.xlsm'.
Inside your search function, whenever a matching file is found, use 'Workbooks.Open' with 'On Error Resume Next' right above it. Call your modification Sub, then use 'ActiveWorkbook.Close SaveChanges:=True' to finish the cycle.
Run Your VBA Macros Seamlessly in WPS Spreadsheet
WPS Office Spreadsheet provides excellent support for VBA macros, allowing you to run complex automation scripts like recursive folder scanning exactly as you would in Microsoft Excel.
- 1. Open WPS Spreadsheet: Launch WPS Office and open a new or existing spreadsheet to act as your master macro workbook.
- 2. Access the Developer Tab: Navigate to the Developer tab on the ribbon. If it is not visible, go to the Options menu to enable the Developer Tools.
- 3. Open the VBA Editor: Click the 'Visual Basic' button on the ribbon or press Alt + F11 to open the WPS Macro Editor interface.
- 4. Paste and Run Your Code: Click 'Insert' > 'Module', paste your recursive search and modification code, and click the 'Run' icon to process your subfolders.
Frequently Asked Questions
Why does my macro crash when opening certain Excel files in subfolders?
This usually happens if a file is password-protected, corrupted, or currently open by another user. Implement 'On Error Resume Next' before the 'Workbooks.Open' command and check the Err.Number to handle these exceptions gracefully.
How can I make the VBA macro search for multiple target strings?
You can modify your IF statement in the search routine to check for multiple conditions using the 'OR' operator, or use an array of strings and loop through it to see if the file path contains any of your target keywords.
Does FileSystemObject work on Mac operating systems?
No, FileSystemObject (Microsoft Scripting Runtime) is a Windows-only feature. If you are writing a macro for Mac, you will need to use MacScript or the built-in 'Dir' function with specific Apple path separators.
Can I run this macro without visibly opening every Excel file on my screen?
Yes. You can add 'Application.ScreenUpdating = False' and 'Application.Visible = False' at the beginning of your script. This allows the files to be processed in the background, which also significantly speeds up the macro's execution time.
