VBA & Macro Problems

Run an Excel VBA Macro on Files in Every Subfolder

Adam DavisAdam Davis Nov 18, 2026 868 views

Question details

Run a VBA macro across the immediate subfolders of a selected master folder and process the Excel files in each one.

Run an Excel VBA Macro on Files in Every Subfolder
Product
Microsoft Excel
Device & OS
not provided
Scenario
The user wants to repair XLSX files and save copies in a RecoveredWB folder within each immediate subfolder.
Observed behavior
Each eligible workbook should be opened with Excel's repair option and saved as a copy in its subfolder's RecoveredWB folder.
Before you start

Use desktop Excel on Windows for this FileSystemObject example, and close the workbooks you plan to process. Save the workbook containing the macro as an XLSM file and enable macros only if you trust the code.

Solution 1Recommended

Use VBA to repair and copy XLSX files in immediate subfolders

Select a master folder and let the macro process XLSX files directly inside each immediate subfolder.

The macro creates a RecoveredWB folder in each immediate subfolder, opens each XLSX file with Excel's repair option, and saves a copy there. It does not process files in the master folder or search deeper nested folders.

Existing files with the same output name are skipped. The macro stops at the first error and displays its description after restoring Excel's settings.

1
Open the VBA editor

In desktop Excel, open your macro-enabled workbook and press Alt+F11. In the VBA editor, choose Insert > Module.

2
Paste the macro

Paste this complete macro into the module: Sub ProcessSubfolderWorkbooks() Dim fso As Object, rootFolder As Object, subFolder As Object, fileItem As Object Dim masterPath As String, outputPath As String, targetPath As String Dim wb As Workbook Dim oldScreenUpdating As Boolean, oldSecurity As Long Dim errorMessage As String With Application.FileDialog(msoFileDialogFolderPicker) .Title = "Select the master folder" If .Show = 0 Then Exit Sub masterPath = .SelectedItems(1) End With oldScreenUpdating = Application.ScreenUpdating oldSecurity = Application.AutomationSecurity On Error GoTo CleanUp Application.ScreenUpdating = False Application.AutomationSecurity = 3 ' Force-disable macros in opened files Set fso = CreateObject("Scripting.FileSystemObject") Set rootFolder = fso.GetFolder(masterPath) For Each subFolder In rootFolder.SubFolders outputPath = fso.BuildPath(subFolder.Path, "RecoveredWB") If Not fso.FolderExists(outputPath) Then fso.CreateFolder outputPath For Each fileItem In subFolder.Files If LCase$(fso.GetExtensionName(fileItem.Name)) = "xlsx" Then targetPath = fso.BuildPath(outputPath, fileItem.Name) If Not fso.FileExists(targetPath) Then Set wb = Workbooks.Open(fileItem.Path, CorruptLoad:=xlRepairFile) wb.SaveCopyAs targetPath wb.Close SaveChanges:=False Set wb = Nothing End If End If Next fileItem Next subFolder CleanUp: errorMessage = Err.Description On Error Resume Next If Not wb Is Nothing Then wb.Close SaveChanges:=False Application.AutomationSecurity = oldSecurity Application.ScreenUpdating = oldScreenUpdating On Error GoTo 0 If Len(errorMessage) > 0 Then MsgBox errorMessage, vbExclamation End Sub

3
Run the macro

Return to Excel, press Alt+F8, select ProcessSubfolderWorkbooks, and click Run. In the folder picker, select the master folder and confirm.

4
Check the output

Open an immediate subfolder in File Explorer, then open its RecoveredWB folder to inspect the saved copies. Check any error message if processing stopped early.

Use VBA to repair and copy XLSX files in immediate subfolders
File scope: This example processes only files with the .xlsx extension. It skips an output file if the same name already exists in RecoveredWB.
Free Microsoft Office alternative

Work with Excel files in WPS Office

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and support for common Excel formats, making it easy to move everyday workbook tasks. This recommendation does not replace Excel's repair macro; verify macro compatibility before migrating VBA workflows.

Open and edit XLSX workbooks in WPS SpreadsheetSupport for common Microsoft Excel formats, including XLSXFamiliar spreadsheet tools for everyday workbook tasksFree, lightweight office suite for moving routine work from Microsoft Office
microsoft office alternative - wps office

Frequently Asked Questions

Does the macro process nested subfolders?

No. It loops through the master folder's immediate subfolders and processes XLSX files directly inside them. Deeper folders are not searched.

What happens if a recovered copy already exists?

The macro checks the RecoveredWB folder for the same file name and skips that file instead of overwriting it.

Can this macro process XLSM or XLS files?

Not as written. The extension check accepts only XLSX files. Supporting other formats requires changing the check and testing the save behavior for each format.

Where do I run the macro in Excel?

After inserting the code in a VBA module, return to Excel, press Alt+F8, select ProcessSubfolderWorkbooks, and click Run.

Will processing continue if a workbook cannot be opened?

No. This example stops at the first error, closes the workbook it was processing if necessary, restores Excel's settings, and displays the error description.