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.