Macro in Excel – Loop Through all Files in a Folder using VBA

0 Flares Filament.io 0 Flares ×

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.

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

6 thoughts on “Macro in Excel – Loop Through all Files in a Folder using VBA

  1. 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!

  2. i am getting rut time error on Line Sheets(2).Range(“a1”).Value = “Stepbystep” and the new File will remain open

Leave a Reply

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