Excel-Access.tips: Excel loop workbooks in folders and subfolders with FSO

This Excel tutorial explains how to loop workbooks in folders and subfolders with FileSystemObject (FSO).

Excel loop workbooks in folders and subfolders

FileSystemObject (FSO) provides an API to access the Windows filesystem such as accessing Drive, TextStram, Folder, File. In this tutorial, since we are discussing how to loop workbook in folders and subfolders, I will only explain the use of Folder and File.

FSO can also be used in other Microsoft Products such as ASP, Word, Access, etc. Therefore you just need to modify the workbook specific Objects in my examples in order to use the code in other products.

You should be able to run FSO in Excel 2013. If you fail to run FSO Object, open VBE (ALT+F11) > Tools > References > Check the box Microsoft Scripting Runtine

After using FSO, you can loop workbooks in a folder and do whatever you want. In this tutorial, I will show some examples which you can copy and modify at ease.

Modify workbooks in folders and subfolders

The below Procedure open and close the workbooks in the folders and subfolders, you can further add your own code to modify the workbooks.

Public Sub openWB()
    Dim FSO As Object
    Dim folder As Object, subfolder As Object
    Dim wb As Object
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    folderPath = "C:\Users\WYMAN\Desktop\testDel"
    Set folder = FSO.GetFolder(folderPath)
    
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
        .EnableEvents = False
        .AskToUpdateLinks = False
    End With
        
    For Each wb In folder.Files
        If Right(wb.Name, 3) = "xls" Or Right(wb.Name, 4) = "xlsx" Or Right(wb.Name, 4) = "xlsm" Then
            Set masterWB = Workbooks.Open(wb)
  'Modify your workbook
            ActiveWorkbook.Close True
        End If
    Next
    For Each subfolder In folder.SubFolders
        For Each wb In subfolder.Files
            If Right(wb.Name, 3) = "xls" Or Right(wb.Name, 4) = "xlsx" Or Right(wb.Name, 4) = "xlsm" Then
                Set masterWB = Workbooks.Open(wb)
 'Modify your workbook
                ActiveWorkbook.Close True
            End If
        Next
    Next
    With Application
        .DisplayAlerts = True
        .ScreenUpdating = True
        .EnableEvents = True
        .AskToUpdateLinks = True
    End With
End Sub

Delete workbook in folder and subfolder

The below Procedure deletes workbooks in a folder and its subfolders.

Public Sub delWB()
    Dim FSO As Object
    Dim folder As Object, subfolder As Object
    Dim wb As Object
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    folderPath = "C:\Users\WYMAN\Desktop\testDel"
    Set folder = FSO.GetFolder(folderPath)
    
    For Each wb In folder.Files
        If Right(wb.Name, 3) = "xls" Or Right(wb.Name, 4) = "xlsx" Or Right(wb.Name, 4) = "xlsm" Then
           FSO.DeleteFile wb, True
        End If
    Next
    For Each subfolder In folder.SubFolders
        For Each wb In subfolder.Files
            If Right(wb.Name, 3) = "xls" Or Right(wb.Name, 4) = "xlsx" Or Right(wb.Name, 4) = "xlsm" Then
                FSO.DeleteFile wb, True
             End If
        Next
    Next
End Sub

Other related FSO Methods

Now that you have understood the basics to loop workbooks, but you may also want to move around the folders or files, click the below articles to read more.

FSO File Methods

FSO Folder Methods