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
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.
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