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

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,
2 comments on “Tom’s Tutorials For Excel: Open, Modify, and Close All Workbooks in a Folder
  1. Jakub says:

    Hello,

    I tried to use your script but when I choose files to be changed location, excel return error – there are no files in this directory. When I run script on any excel file excel return MsgBox that macro trying to make modyfications in this excel file and later there are no other files in this location. I suppose I am doing sth wron with Dir, ChDir or SourcePath. It is possible to adress folder to make changes in file different way?

    • Tom Urtis says:

      Not sure I understand your question because I know the code works.
      To point to a folder that is not (and probably would not be) C:/Your/File/Path/,
      change the line
      SourcePath = “C:/Your/File/Path/”
      to whatever your source folder path is.
      Otherwise, please explain further what the problem is that you are still having.

Leave a Reply to Jakub Cancel reply

Your email address will not be published. Required fields are marked *

*