• Skip to main content
  • Skip to primary sidebar
  • Skip to footer

Microsoft Excel Tips and Tricks - Computergaga

  • Home
  • Excel Online Courses
  • Excel Functions List
  • Free Downloads
  • Blog
  • Tutorials
  • About
Home / VBA / Loop Through all Files in a Folder using Excel VBA

Loop Through all Files in a Folder using Excel VBA

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

Related Posts:

  • SWITCH function feature
    Excel SWITCH Function - Nested IF Function Alternative
  • Case sensitive XLOOKUP feature
    Case Sensitive Lookup with XLOOKUP in Excel
  • Live currency exchange rate in Excel thumbnail
    LIVE Currency Exchange Rate in Excel
  • Copy a formula down in Excel
    How to Copy a Formula Down in Excel

Reader Interactions

Comments

  1. Stuart Allum says

    29 August 2013 at 10:25 am

    Great bit of code, very cool.
    would be good to add a end message “all files now completed”

    Reply
    • Derek says

      28 April 2017 at 6:03 pm

      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.

      Reply
      • computergaga says

        29 April 2017 at 6:47 am

        Thanks Derek

        Reply
  2. Paul says

    11 October 2014 at 7:43 pm

    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.

    Reply
    • Paul says

      11 October 2014 at 8:31 pm

      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!

      Reply
  3. Nabeel says

    23 February 2016 at 10:56 am

    This helped big time.

    Reply
  4. nitin says

    28 February 2016 at 2:37 pm

    i get run time error 11
    division by zero
    in this line
    MyFolder = .SelectedItems(1) & “ \ ” ‘Assign selected folder to MyFolder

    Reply
    • j says

      4 August 2017 at 4:27 pm

      There shouldn’t be a space after the \

      Reply
  5. ravikumar says

    14 July 2016 at 2:21 pm

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

    Reply
  6. Sachin says

    10 February 2017 at 3:15 pm

    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?

    Reply
    • computergaga says

      10 February 2017 at 3:38 pm

      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

      Reply
      • Sachin says

        10 February 2017 at 11:10 pm

        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

        Reply
        • computergaga says

          11 February 2017 at 8:52 pm

          Your welcome Sachin

          Reply
  7. Alex kim says

    13 November 2017 at 6:44 am

    Hi,
    Is there a way add macro to create a new sheet in all file in a folder?(add coding to your code)
    Thanks

    Reply
    • computergaga says

      14 November 2017 at 1:22 pm

      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.

      Reply
  8. bob says

    27 April 2021 at 7:13 pm

    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 …

    Reply
    • Alan Murray says

      29 April 2021 at 7:59 am

      Thanks for the suggestion, Bob.

      Reply
  9. Chris says

    5 April 2022 at 8:04 pm

    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”

    Reply
  10. Ash says

    18 August 2022 at 10:44 pm

    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

    Reply
    • Alan Murray says

      20 September 2022 at 10:22 am

      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

      Reply
  11. Pirabu says

    12 October 2022 at 12:42 am

    Simply wonderful. Been searching a long time for this. You saved me.
    Thank You very much

    Reply
    • Alan Murray says

      16 October 2022 at 9:01 pm

      No worries. Happy to help. Thank you.

      Reply
  12. Mohammed Eshraq Rahman says

    21 October 2022 at 8:02 pm

    Hello Alan,
    What changes I would have to make if the files are MS Word documents?

    Reply
    • Linda says

      6 March 2023 at 6:30 pm

      I have this same question and would like to adapt this script for MS Word instead of Excel!

      Reply
      • Alan Murray says

        27 March 2023 at 11:54 am

        Hi Linda and Mohammed,
        Both applications use VBA so expect that the code would be very similar. I do not use VBA in Word so my knowledge is not tremendous, but I would expect that you could use the same, except replace Workbooks in both the variable instance and the open workbook instance with Documents. And obviously not the range A2 bit in the code.
        The loop and other framework should be the same. It is only that you are referencing documents and not workbooks.

        Reply
  13. Lucy says

    17 November 2022 at 11:43 pm

    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?

    Reply
  14. Jack says

    17 May 2023 at 7:53 pm

    It doesn’t prompt me with a dialog box. It looks like it just stored the folder in memory

    Reply
  15. John Intech says

    11 January 2024 at 7:58 pm

    It’s so close to what I need!?!?!?! I’m trying to find a pc of code to change the right footer in every workbook and every sheet in those workbooks in a folder to a specific text string and then save. I’m an excel amateur. Think you could help me out?

    Reply

Leave a Reply Cancel reply

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

Primary Sidebar

Recent Posts

  • Excel SWITCH Function – Nested IF Function Alternative
  • Sum Formulas Only in Excel
  • How to Calculate Age in Excel
  • Custom Sort in Excel
  • Case Sensitive Lookup with XLOOKUP in Excel
Advanced Excel Success book

Footer

Contact

[email protected]

LinkedIn Twitter YouTube

Useful Links

  • Home
  • Excel Online Courses
  • Tutorials
  • Sitemap
  • Privacy Policy
  • Jobs by Jooble

Course Topics

  • Blog
  • Charts
  • Courses
  • Dashboards
  • Downloads
  • Excel Tips
  • Formulas
  • Mail Merge
  • Office
  • PivotTables
  • Power BI
  • Power Pivot
  • Power Query
  • VBA

Join Our Mailing List

© Copyright 2009 Computergaga · All Rights Reserved ·