How to Loop Through Files in Excel VBA

08 Jun How to Loop Through Files in Excel VBA

Howdee! One of the most useful pieces of code I ever learned was the ability to loop through files in a folder with Excel VBA and pull data from those files (check out the video if that’s more your thing). If you’re an analyst, I’m sure you’ve had the unfortunate experience of opening multiple template submissions and copy/pasting information from them to a summary file.  Being able to write a bit of code to loop through multiple files and grab that information saves mountains of times and frees up more time to review the data.

First, you’ll need to open the code editor by pressing ‘alt + F11’ and right click on the workbook project you want to run your code from, and insert a module:

You can rename your module in the properties window if you like.  I usually like to name it something to describe what code will be contained within the module.

Now you’ll need to paste this code within that module:

    Sub File_Loop_Example()
    'Excel VBA code to loop through files in a folder with Excel VBA

    Dim MyFolder As String, MyFile As String

    'Opens a file dialog box for user to select a folder

    With Application.FileDialog(msoFileDialogFolderPicker)
       .AllowMultiSelect = False
       .Show
       MyFolder = .SelectedItems(1)
       Err.Clear
    End With

    'stops screen updating, calculations, events, and statsu bar updates to help code run faster
    'you'll be opening and closing many files so this will prevent your screen from displaying that

    Application.ScreenUpdating = False
    Application.DisplayStatusBar = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual

    'This section will loop through and open each file in the folder you selected
    'and then close that file before opening the next file

    MyFile = Dir(MyFolder & "\", vbReadOnly)

    Do While MyFile <> ""
        DoEvents
        On Error GoTo 0
        Workbooks.Open FileName:=MyFolder & "\" & MyFile, UpdateLinks:=False
        ''''''''''''ENTER YOUR CODE HERE TO DO SOMETHING'''''''''
        MsgBox MyFile
    0
        Workbooks(MyFile).Close SaveChanges:=False
        MyFile = Dir
    Loop

    'turns settings back on that you turned off before looping folders

    Application.ScreenUpdating = True
    Application.DisplayStatusBar = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationManual

    End Sub
    

What Does This Code Do?

As the title suggests, this code will loop through files in a folder with Excel VBA, but what is actualy happening? This code snippet will start with a dialog box that allows the user to select a folder. It will prevent the user from selecting a specific file to prevent breaking the code.  Then, the code will loop through all files within a folder, opening each, performing some action you tell it to, and then close the workbooks without saving changes (if you’re making changes to the workbook you open, you’ll want to change the “SaveChanges:=False” to “SaveChanges:=True”). It’s that simple!

This bit of code saves me hours (I literally mean hours) every month when collecting data from many different files and I use this in many of my other Excel VBA projects. Note that I’ve included several lines at the beginning and end of the sub that turn off various application settings and then turns them back on. This helps speed up the process tremendously. Without these lines, this code would take quite a bit of time to run. It’s best practice to save the users settings and return them if someone other than yourself will be running the code.

Optimizing The Process

There are some things you’ll want to consider when running this code. Working with many files, or large files, can make this process take a while. When I run on 35-40 files around 5MB in size, I typically start and go do something for a few minutes (usually 3-4 min). I also advise not to run this code on a folder that lives in the cloud. It works, but the files must download one at a time and slows it down quite a bit. I usually create a temp folder on my Desktop to download the files to. Downloading all files at once is quick and then it speeds up my code about 300%. Worth the extra effort.

Lastly, just note that, since you’re disabling screen updating to improve code performance, you cannot use Excel while this code runs.

Now go save yourself some time and let me know if you have questions!

P.S. If you’d rather download my loop through files in a folder with Excel VBA example file, you can do so here. The example file loops through all files and returns the name of the file and the folder it’s in so you can verify it works. This is only available to subscribers. It’s free and I’ll never share your information with anyone for any reason.

Cheers,

R