Looping Over Files

One of the greatest benefits of using VBA with Excel is that you can automate tasks that can ordinarily be time-consuming. One form of automation that can be especially useful is to automatically open a set of files in a folder and open each of the files, make a change to the file content, and save the file. This can be accomplished quite easily with VBA in Excel, especially if the files correspond to Excel spreadsheets.

In this page, we will work through an example of modifying a set of spreadsheet files via VBA. Using this example as a guide, you can modify the code to fit your circumstances. The files associated with this exercise are in a zip archive and can be downloaded here:

loopingfiles.xlsm
samplefiles.zip

If you wish to follow along, please download and unzip the files in the zip archive to a folder named "samplefiles". Then open the spreadsheet file ("loopingfiles.xlsm") in Excel.

Renaming the Files

When you unzip the samplefiles.zip archive, you should see the following set of files:

In this case, each of these files is empty, but in other cases they may contain data. Our objective is to open each of these files and copy a table to the main sheet and then save the changes. \

Whenever you loop over files, you must have some systematic way of determining the names of the files in the folder. There used to be a FileSearch object that would list all of the files in a directory, but it was deprecated (discontinued) by Miscrosoft because it was being used to write viruses. So the simplest thing to do now is to name the files so that we can formulate the file name in code as we iterate through a For loop from 1 to the number of files. Fortunately, it is rather easy to rename the files as shown. If you have an existing set of files to rename, you can do it as follows:

1. Select all of the files
2. Right-click on the files and select the Rename command.
3. Enter a common name ("worksheet" in the example shown above) and hit the return key.

At this point all files are renamed as follows:

This format is easier to recreate using code (see below).

Input Options

When you open the spreadsheet file you will see the main page:

The inputs to the code are in three cells: B11, B13, and B15. B11 contains the path to the folder containing the files you wish to modify. B13 contains the prefix used when naming the files. Compare to file list shown above. B15 contains the number of files. Please note that cells B11, B13, and B15 have been named folderlocation, prefix, and nfiles, respectively. You may need to change these values before proceeding.

Before looking at the code, click on the Sheet2 tab and note the contents:

For our example problem, we will be copying this table from the loopingfiles.xlsm workbook to the first sheet in each of the files in the samplefiles folder and saving the changes.

Code

Next we will look at the source code associated with the Fix Files button.

Private Sub cmdFixFiles_Click()
Dim myrow As Integer
Dim i As Integer
Dim nfiles As Integer
Dim filepath As String

'Set the default working directory
ChDir Range("folderlocation")

'Loop through each of the files in the folder
nfiles = Range("nfiles")
For i = 1 To nfiles

    'Copy the header and table on Sheet2 to the clipboard
    Sheets("Sheet2").Range("A1:E18").Copy

    'Formulate a text string identifying the full path to file i
    filepath = Range("startpath") & "\" & Range("prefix") & " (" & i & ").xlsx"
    
    'Open the file
    Workbooks.Open Filename:=filepath

    'Select the upper left cell and past the clipboard contents
    ActiveSheet.Range("A1").Select
    ActiveSheet.Paste
    
    'Fit the column widths
    ActiveSheet.Columns("B:B").EntireColumn.AutoFit
    ActiveSheet.Columns("C:C").EntireColumn.AutoFit
    ActiveSheet.Columns("D:D").EntireColumn.AutoFit
    ActiveSheet.Columns("E:E").EntireColumn.AutoFit
    
    'Select one of the cells so that the entire table is no longer selected (optional)
    ActiveSheet.Range("B4").Select
    
    'Save and close the file
    ActiveWorkbook.Save
    ActiveWorkbook.Close

    'Go to the next file
Next i

'Exit cut/copy mode (optional)
Application.CutCopyMode = False

End Sub

Each of the steps in the code is documented with a comment. Note how the folder location, prefix, and file number are used to generate a complete path to the file as shown on this line:

'Formulate a text string identifying the full path to file i
filepath = Range("startpath") & "\" & Range("prefix") & " (" & i & ").xlsx"

Also, note that once you open another workbook, you have to be very careful how you reference cells and ranges. For example, if you reference cell A4, to which workbook does that apply? To ensure that there is no confusion, you should add the ActiveSheet or ActiveWorkbook prefix to all references to the external workbook after you open it. If ou need to refer to the current workbook (the one containing the code) while the other workbook is open, use the prefix ThisWorkbook before all sheet or range references.

If you are not sure how to structure the intercation between your two workbooks, you can always record a macro and perform the steps you wish to perform and then examine the macro code and adapt it to the sample shown above.

You may wish to try running the code above. You will see the screen flash once for each sample file as the code runs. After running the code, open each of the sample files to verify that the table was properly copied.