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.
Craig Papa says
Hi, thanks for the code. it works well. but i have a small issue, it stops after the first sub folder within a folder. how would i get it to continue to go into all folders, sub folder and sub sub folders to see if the file exists?
zhaojun tao says
The code here does not go into the subfolder of sublder,
I just find the solution here:https://stackoverflow.com/questions/22645347/loop-through-all-subfolders-using-vba
Thanks for the code. I am having trouble incorporating FSO.CopyFile to copy xls files from each subfolder. Any suggestion?
Problems may be encountered if the file does not exist in a subfolder or already exists in the destination folder.
Alwin Selvaraj says
Really helpful. It would be great if you can provide the code to loop through subfolders of subfolder.
Alan Murray says
Thank you, Alwin. I think I have that code somewhere on my site. I’ll need to dig it out.