Excel loop workbooks in folders and subfolders with FSO

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

You may also want to read:

Excel VBA Dir Function to loop through workbooks in folder

Add password to all Excel workbook in folder

Excel VBA search text in multiple Workbooks in folder

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

loop_through_workbook

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

Extract file names in folder and subfolders

Suppose you have a lot of files in a folder and subfolders and you want to get the file names in the worksheet so that you can check what files you have got, then you can use this VBA to extract file names.

Public Sub extractFileName()
  Dim FSO As Object
  Dim folder As Object, subfolder As Object
  Dim wb As Object

  Set FSO = CreateObject("Scripting.FileSystemObject")
  folderPath = "C:\test\"
  Set folder = FSO.GetFolder(folderPath)

  r = 1
  For Each wb In folder.Files
    ActiveSheet.Range("A" & r) = wb.Name
    r = r + 1
  Next

  For Each subfolder In folder.SubFolders
    For Each wb In subfolder.Files
      ActiveSheet.Range("A" & r) = wb.Name
    r = r + 1
    Next
  Next
End Sub

Example
I want to get the file names in the test folder plus file names in the subfolder (one level down).

Run the Macro

Further Workbook actions you may want to do

After you have looped through workbooks, you may want to do the followings

Auto run Macro when workbook is opened

Consolidate worksheets into one worksheet

Extract columns with specific header to new workbook

Find all external links and broken links in workbook

Loop through all worksheets in the same workbook

Refresh closed workbook (links / Pivot Tables)

 Outbound References

https://msdn.microsoft.com/en-us/library/7kby5ae3%28v=vs.84%29.aspx

Leave a Reply

Your email address will not be published.