One of the main reasons to create a macro in Excel is to make a repetitive task easier and quicker. Such a task may be to loop through all files in a folder and perform an action on each one.
The code below will display the folder picker dialog box to allow the user to specify the folder (directory) that they want to use. It will then loop through all the files within that folder. It will open the workbook, perform an action and then close it saving the changes made.
Comments have been used to explain different areas of the code.
Techniques used to Loop Through All Files in a Folder
The DIR function is used to return the first file from a specified folder or directory. It can then be used to iterate through each file in the folder.
An application FileDialog called msoFileDialogFolderPicker is used to display a dialog box to enable the user to select the folder they want to use.
The action this macro performs is to write the number 60 into cell A1 of the second sheet in each workbook. This is a silly and simple example and can easily be checked to ensure that your macro worked as required.
This statement should be replaced with the actions that you need your macro in Excel to perform.
Sub AllWorkbooks()
Dim MyFolder As String ‘Path collected from the folder picker dialog
Dim MyFile As String ‘Filename obtained by DIR function
Dim wbk As Workbook ‘Used to loop through each workbook
On Error Resume Next
Application.ScreenUpdating = False
‘Opens the folder picker dialog to allow user selection
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Please select a folder"
.Show
.AllowMultiSelect = False
If .SelectedItems.Count = 0 Then ‘If no folder is selected, abort
MsgBox "You did not select a folder"
Exit Sub
End If
MyFolder = .SelectedItems(1) & "\" ‘Assign selected folder to MyFolder
End With
myfile = Dir(MyFolder) ‘DIR gets the first file of the folder
‘Loop through all files in a folder until DIR cannot find anymore
Do While myfile <> ""
‘Opens the file and assigns to the wbk variable for future use
Set wbk = Workbooks.Open(Filename:=MyFolder & myfile)
‘Replace the line below with the statements you would want your macro to perform
Sheets(2).Range("a1").Value = 60
wbk.Close savechanges:=True
MyFile = Dir ‘DIR gets the next file in the folder
Loop
Application.ScreenUpdating = True
End Sub
Other Excel VBA tutorials
- Use the OnTime Method in Excel VBA
- Create a splash screen in Excel
- Spell check a protected Excel worksheet
Great bit of code, very cool.
would be good to add a end message “all files now completed”
MsgBox(“All files now completed”)
Tack that onto the end of the code and you’re golden. I know this is a couple years after your comment, but in case someone else would like to do the same I figured I’d post it.
Thanks Derek
Hello, I’m getting a syntax error when I try to run this code. The first “Dim” line of code is being highlighted for troubleshooting…any idea what I may be doing wrong?
Thanks.
Oops, sorry…disregard my question above. I figured it out. Turns out it just wasn’t recognizing the quotes and apostrophes when I copied and pasted the code…just had to manually type over those symbols. Works now – thanks!
This helped big time.
i get run time error 11
division by zero
in this line
MyFolder = .SelectedItems(1) & “ \ ” ‘Assign selected folder to MyFolder
There shouldn’t be a space after the \
i am getting rut time error on Line Sheets(2).Range(“a1”).Value = “Stepbystep” and the new File will remain open
This code works very well. Thanks Alan for the explanations!
I’m placing the main workbook in the same folder where all the files are located. How can I exclude the main workbook from the actions taken by the VBA?
Thanks Sachin,
You just need a little conditional test and skipping action. For example, if your main file is called Book1.xlsx the lines below can go after the Do While line.
If MyFile = “Book1.xlsx” then
goto MainWorkbook
End If
And then enter MainWorkbook: before the MyFile – Dir line
That worked like a charm. Thank you so much Alan! And I appreciate your swift reply. You programmers have a kind heart to help people like me.
Thanks Sachin
Your welcome Sachin
Hi,
Is there a way add macro to create a new sheet in all file in a folder?(add coding to your code)
Thanks
Sure Alex. In the code when the workbook is opened and assigned to the wbk variable. You could insert the line.
Sheets.Add After:=Worksheets.Count
This should add a new sheet to the end of all the sheet in the workbook.
Hello,
would suggest to replace line with »\ » with application.pathseparator so
myfolder = .selecteditems(1) & Application.PathSeparator
this is better, when file is used on Apple Mac …
Thanks for the suggestion, Bob.
This looks like exactly what I need. Thank you for the codes and explanations. One question. How can I modify this to only select files that contain certain words in the title? The folder I have set up has several different files in it but I only want the code to loop through files that contain “daily_file_reconciliation”
Hello
It does not prompt me for the dialog box to select folder and keeps giving me the message “you did not select a folder”
This is the part i have from your code.
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = “Please select a folder”
.Show
.AllowMultiSelect = False
MsgBox (Title)
If .SelectedItems.Count = 0 Then ‘If no folder is selected, abort
MsgBox “You did not select a folder”
Exit Sub
End If
The following code works fine for the selection of a folder Ash. I removed a couple of lines as you can see and added the ‘End With’ line.
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = “Please select a folder”
.Show
.AllowMultiSelect = False
If .SelectedItems.Count = 0 Then
MsgBox “You did not select a folder”
Exit Sub
End If
End With
Simply wonderful. Been searching a long time for this. You saved me.
Thank You very much
No worries. Happy to help. Thank you.
Hello Alan,
What changes I would have to make if the files are MS Word documents?
This is so clear and simply explained, thank you so much! Its absolutely exactly what I was looking for and works perfectly for one file but then doesn’t loop to the next… its driving me mad, any ideas what might cause this?