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 can easily be checked to ensure that your macro worked as required. This statement should be replaced with the statements that you need your macro in Excel to run.
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
.Title = “Please select a folder”
.AllowMultiSelect = False
If .SelectedItems.Count = 0 Then ‘If no folder is selected, abort
MsgBox “You did not select a folder”
MyFolder = .SelectedItems(1) & “\” ‘Assign selected folder to MyFolder
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
MyFile = Dir ‘DIR gets the next file in the folder
Application.ScreenUpdating = True
Other Excel VBA tutorials
- Use the OnTime Method in Excel VBA
- Create a splash screen in Excel
- Spell check a protected Excel worksheet