Tom’s Tutorials For Excel: Open, Modify, and Close All Workbooks in a Folder

Tom’s Tutorials For Excel: Open, Modify, and Close All Workbooks in a Folder

This topic touches on a surprise we all got when Microsoft released version 2007, which stopped supporting the VBA FileSearch method. When you need to open all the workbooks in a folder, modify them in some way, then save and close them, the following macro will work in all versions of Excel from 2000 through and including 2010. Modify for the SourcePath variable.

Sub OpenAllWorkbooks()

'Declare and define variables.
Dim objWB As Workbook, SourcePath As String, wbName As String
SourcePath = "C:/Your/File/Path/"

'Identify the directory of interest.
ChDir SourcePath
'Identify the workbook extension of interest.
wbName = Dir("*.xls*")

'Open a With structure for the Application object and prepare Excel.
With Application
.ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts = False
.AskToUpdateLinks = False

'Open a Do Loop to open all workbooks in the SourcePath.
Do

'Error bypass in case no workbooks exist in the SourceFolder.
On Error Resume Next
Set objWB = Workbooks.Open(SourcePath & wbName)
If Err.Number <> 0 Then
Err.Clear
With Application
.AskToUpdateLinks = True
.DisplayAlerts = True
.EnableEvents = True
.ScreenUpdating = True
End With
MsgBox "There were no files with the specified extension" & vbCrLf & _
"in the path " & SourcePath & ".", 48, "Cannot continue, nothing to open."
Exit Sub
End If

'Your actual code to modify the workbook would go here.
MsgBox "Workbook that is open now:" & vbCrLf & _
ActiveWorkbook.Name & vbCrLf & vbCrLf & _
"Your code would go here to do something with these workbooks.", , "Example"

'Save and close the workbook and move on to the next one.
objWB.Close True
wbName = Dir
Loop While wbName <> ""

'Close the With structure for the Application object and reset Excel.
.AskToUpdateLinks = True
.DisplayAlerts = True
.EnableEvents = True
.ScreenUpdating = True
End With

'Advise the user that the macro is complete.
MsgBox "All workbooks in " & SourcePath & vbCrLf & _
"have been opened and closed.", 64, "OK, all done !!"

End Sub