Loop Through Subfolders with FileSystemObject

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.