Introduction | Example | Tutorial | Applications | Comments
Introduction - VBA FileSystemObject
This tutorial will show you how to use the VBA FileSystemObject to traverse folders and subfolders and list all the files meeting specific conditions.
If you haven’t already done so, check out our Introduction to the VBA FileSystemObject for a general introduction to the FSO and all its functions.
In this tutorial, we present a recursive procedure which outputs all the paths to files with specific extensions in a specified folder and its subfolders. This procedure can easily be modified to return other results based on different criteria, as well. However, please start by testing the procedure as is and always make sure you test the output of your modified procedure before you start actually processing files with it!
Example - VBA FileSystemObject
Listing files in folders and subfolders using VBA
The procedure below prints all the found file paths in column A of the active sheet. This makes it easier for you to verify that the found paths only refer to files that meet your criteria!
Before you run the code, do this:
- Add a new sheet to your workbook.
- Make sure the blank sheet you just inserted is the active sheet and then execute the
The macro will list all the files in your folder and subfolders that match your extension to this new sheet.
Insert the code below into a standard code module:
' Variable declarations Dim blNotFirstIteration As Boolean Dim Fil As File Dim hFolder As Folder, SubFolder As Folder Dim FileExt As String Dim FSO As Scripting.FileSystemObject ' Recursive procedure for iterating through all files in all subfolders ' of a folder and locating specific file types by file extension. Sub FindFilesInFolders(ByVal HostFolder As String, FileTypes As Variant) '(1) This routine uses Early Binding so you must add reference to Microsoft Scripting Runtime: ' Tools > References > Microsoft Scripting Runtime '(2) Call procedure using a command like: ' Call FindFilesInFolders("C:\Users\MHS\Documents", Array("xlsm", "xlsb")) If FSO Is Nothing Then Set FSO = New Scripting.FileSystemObject Set hFolder = FSO.GetFolder(HostFolder) ' iterate through all files in the root of the main folder If Not blNotFirstIteration Then For Each Fil In hFolder.Files FileExt = FSO.GetExtensionName(Fil.Path) ' check if current file matches one of the specified file types If Not IsError(Application.Match(FileExt, FileTypes, 0)) Then ' **************************************** ' Insert your code here ' **************************************** Cells(WorksheetFunction.CountA(Range("A:A")) + 1, 1) = Fil.Path End If Next Fil ' make recursive call, if main folder contains subfolder If Not hFolder.SubFolders Is Nothing Then blNotFirstIteration = True Call FindFilesInFolders(HostFolder, FileTypes) End If ' iterate through all files in all the subfolders of the main folder Else For Each SubFolder In hFolder.SubFolders For Each Fil In SubFolder.Files FileExt = FSO.GetExtensionName(Fil.Path) ' check if current file matches one of the specified file types If Not IsError(Application.Match(FileExt, FileTypes, 0)) Then ' **************************************** ' Insert your code here ' **************************************** Cells(WorksheetFunction.CountA(Range("A:A")) + 1, 1) = Fil.Path End If Next Fil ' make recursive call, if subfolder contains subfolders If Not SubFolder.SubFolders Is Nothing Then _ Call FindFilesInFolders(HostFolder & "\" & SubFolder.Name, FileTypes) Next SubFolder End If blNotFirstIteration = False End Sub
Tutorial - VBA FileSystemObject
How to use this macro to list files in folders and subfolders
As we discussed in our Introduction to the VBA FileSystemObject tutorial, this macro uses Early Binding so you must add reference to Microsoft Scripting Runtime by navigating to Tools > References > Microsoft Scripting Runtime from your Visual Basic Editor. The comments section of my Introduction tutorial shows you how to use late binding so you wouldn’t have to add this reference but I chose not to so the “Auto List Members” feature will autofill for me. The choice is up to you!
You invoke the procedure using a macro like this:
Sub List_Files_In_SubFolders() Call FindFilesInFolders("C:\Users\MHS\Documents", Array("xlsm", "xlsb")) End Sub
The first argument,
The second parameter,
Write better macros in half the time
I see people struggling with Excel every day and I want to help. That's why I'm giving away my personal macro library for free. This powerful gift lets you automatically import all my macros directly into your spreadsheets with just one click.
Note, depending on the number of files in the search folder and its subfolders, the procedure may take a long time to complete. Please be patient, if you’re handling a large number of files.
In the example above, you’ll notice that all variable declarations are made outside the scope of the procedure, i.e. at the top of the code module. This is because the procedure is a recursive algorithm that keeps calling itself every time it finds a subfolder in the current directory.
If you run the code as is, it will return the paths to all the .xlsm and .xlsb files located in the main search folder and its subfolders. From this starting point, you can easily modify the code to include a call to another procedure which processes each file that meets your criteria. Simply insert your procedure call both places where it says “Insert your code here”, like this:
You can also comment out each instance of the following line when you’re ready to begin processing your own files:
Cells(WorksheetFunction.CountA(Range("A:A")) + 1, 1) = Fil.Path
This line simply prints the files the macro finds to your active sheet so you can see if it’s identifying the files the way you want it to.
To demonstrate use of the GetExtensionName method, this macro has been set up to print all the files with certain extensions, but you can easily modify it to list every file in all subfolders if you’d like.
CAUTION:When you open a Microsoft Office file (e.g. in Excel or Word), a temporary file with the same file extension will automatically be created in the same folder as the original file. While the original file is open, temporary Microsoft Office files are locked for editing, reading and writing and if you programmatically try to process such a file with VBA, you’ll get a run-time error!
Since temporary Microsoft Office files are always prefixed with a tilde (~) sign, you can use this fact to filter out these “false positives”. To do that, replace both instances of
If Not IsError(Application.Match(FileExt, FileTypes, 0)) Then
If Not IsError(Application.Match(FileExt, FileTypes, 0)) And Not Left(Fil.Name, 1) = "~" Then
Application Ideas - VBA FileSystemObject
Reasons you may want to traverse folders and subfolders
Traversing folders and subfolders with VBA and locating files based on your own specified criteria is particularly powerful when you want to
- Determine how many files of a specific type are present in the specified folder and its subfolders.
- Delete junk files that eat up space on your drive.
- Find new files of a specific type made by coworkers or other applications, e.g. on a shared network drive.
- Automatically make changes to many files that meet your criteria in one go (batch processing).
By altering the parameters of the
FindFilesInFolders procedure and/or the conditional statements in it, you can tailor it exactly to your needs, but please make sure you test your modified procedure thoroughly before using it to actually modify files! If possible, make a full backup of your files first. If you implement changes to the procedure correctly, it will make your life a whole lot easier, but if you do it incorrectly, the opposite could easily be the case.
Look forward to future tutorials where you’ll learn about other specific applications of the FSO and how to incorporate the necessary safeguards into them!
If you haven’t already done so, please subscribe to my free wellsrPRO VBA Training Program. What are you waiting for? You’ll love the great VBA content I send your way! Share this article on Google+, Twitter and Facebook, then leave a comment below and let’s have a discussion.This article was written by Michael H. Sorensen on behalf of The VBA Tutorials Blog.
About The VBA Tutorials Blog
The VBA Tutorials Blog was created by Ryan Wells, a Nuclear Engineer and professional VBA Developer. He created this site to help others learn to write macros in Excel. That's why he developed a unique 3-part free Excel training program to help others quickly learn VBA in a natural setting: right inside Excel.Follow