A common Excel VBA requirement is to work with different files and folders of a directory. It may be that you need to search and open a file, list the names of files from a folder, print all the files from a folder, or even delete files.
The different tasks you may need to perform are endless, but fortunately Excel VBA provides an easy way to access and perform operations on the files and folders of a directory. This easy way is known as FileSystemObject.
To use FileSystemObject, otherwise known as FSO, you first need to create an instance of FileSystemObject. You can then access some of the objects from its model such as File and Folder to perform different tasks.
Macro to Loop Through Subfolders of a Folder
The code below loops through the subfolders of a folder. In this example code, we are looping through the subfolders looking for a particular file called Accounts.xlsx. This is just an example and this macro can be adapted easily to accomplish your own needs.
In this code the file is opened, range A2:F10 is copied into the current file, and then the file is saved and closed. A basic operation to demonstrate the technique of looping through the subfolders in a directory.
Sub LoopSubfoldersAndFiles() Dim fso As Object Dim folder As Object Dim subfolders As Object Dim MyFile As String Dim wb As Workbook Dim CurrFile As Object With Application .ScreenUpdating = False .EnableEvents = False .Calculation = xlCalculationManual End With Set fso = CreateObject("Scripting.FileSystemObject") Set folder = fso.GetFolder("C:\Users\Trainee1\Desktop\2016\") Set subfolders = folder.subfolders MyFile = "Accounts.xlsx" For Each subfolders In subfolders Set CurrFile = subfolders.Files For Each CurrFile In CurrFile If CurrFile.Name = MyFile Then Set wb = Workbooks.Open(subfolders.Path & "\" & MyFile) Range("A2:F10").Copy ThisWorkbook.Sheets(1).Range("A2") wb.Close SaveChanges:=True End If Next Next Set fso = Nothing Set folder = Nothing Set subfolders = Nothing With Application .EnableEvents = True .Calculation = xlCalculationAutomatic .ScreenUpdating = True End With End Sub
Early in the code the instance of FileSystemObject is created. The Folder and Subfolders variables are then set. The GetFolder method is used for this.
We then have a loop for the subfolders of a folder, and a interior loop for the files within that folder.
The code finishes with the variables being released from memory by setting them to nothing.
More Excel VBA Tutorials
- Format all instances of certain words in a cell using VBA
- Hangman game created using Excel VBA
- Macro to email the Excel workbook as an attachment
- Export all sheets as PDF using VBA