Process Multiple Excel Workbooks in a Folder with VBA
Question details
Process workbooks in a folder with an Excel VBA macro that formats column E, replaces its formulas with values, and deletes specified columns.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Apply the same changes to the first worksheet of each workbook in a folder.
- Observed behavior
- Column E should display currency amounts and contain values instead of formulas; the original columns A, J, L, and M should be deleted.
Put copies of the workbooks in a test folder and note its full path. Check that the amounts in column E are numeric, because changing the number format does not convert text to numbers.
Run a VBA macro on each workbook in the folder
Use this macro to modify and save the first worksheet of each matching workbook.
The macro replaces formulas with values only in column E, then deletes the original columns from right to left. Deleting columns can affect formulas elsewhere in a workbook.
The example uses a dollar format and does not refresh external links. Change the number format if you need a different currency.
Open Excel and press Alt+F11. In the VBA editor, select Insert > Module in a workbook where you can save and run the macro.
Paste the following code into the module, then replace the folder path with your test folder path, including the final backslash: Sub ProcessWorkbooks() Const folderPath As String = "C:\MYWORKSHEETS\" Dim fileName As String Dim wb As Workbook Dim ws As Worksheet Dim cellsInE As Range Dim previousScreenUpdating As Boolean Dim errorMessage As String previousScreenUpdating = Application.ScreenUpdating On Error GoTo Failed Application.ScreenUpdating = False fileName = Dir(folderPath & "*.xls*") Do While fileName <> "" If Left$(fileName, 2) <> "~$" And _ StrComp(folderPath & fileName, ThisWorkbook.FullName, vbTextCompare) <> 0 Then Set wb = Workbooks.Open(folderPath & fileName, UpdateLinks:=0) If wb.ReadOnly Then Err.Raise vbObjectError + 1, , "Workbook is read-only: " & fileName Set ws = wb.Worksheets(1) Application.Calculate ws.Columns("E").NumberFormat = "$#,##0.00" Set cellsInE = Intersect(ws.UsedRange, ws.Columns("E")) If Not cellsInE Is Nothing Then cellsInE.Value = cellsInE.Value ws.Columns("M").Delete ws.Columns("L").Delete ws.Columns("J").Delete ws.Columns("A").Delete wb.Close SaveChanges:=True Set wb = Nothing End If fileName = Dir Loop Application.ScreenUpdating = previousScreenUpdating MsgBox "Processing complete." Exit Sub Failed: errorMessage = Err.Description On Error Resume Next If Not wb Is Nothing Then wb.Close SaveChanges:=False Application.ScreenUpdating = previousScreenUpdating MsgBox "Processing stopped: " & errorMessage End Sub
Place the cursor inside ProcessWorkbooks and press F5. Open several processed files and check the first sheet's amounts, values, and remaining columns before using the macro on other files.
Edit Excel Workbooks with WPS Spreadsheet
WPS Office is a free, lightweight Office alternative with a familiar interface for opening and editing Excel workbooks. Its format compatibility makes it easy to move existing files into WPS; VBA macro support can vary by version and setup.

Frequently Asked Questions
Does the macro process every worksheet in each workbook?
No. The line Set ws = wb.Worksheets(1) selects only the first worksheet in each workbook.
Does the macro replace formulas throughout the workbook?
No. It replaces formulas with their current values only in the used portion of column E on the first worksheet.
Why are the columns deleted from right to left?
Deleting M, L, J, and then A prevents earlier deletions from shifting the positions of columns that still need to be removed.
Will formatting column E convert text into numbers?
No. The NumberFormat property changes how numeric values are displayed; numeric text must be converted separately.
What happens if a workbook cannot be processed?
The macro stops, closes the current workbook without saving its changes, and displays an error message. Workbooks processed earlier remain saved.




