Introduction | Example | Tutorial | Applications

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

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

Make powerful macros with our free VBA Developer Kit

This is actually pretty neat. If you have trouble understanding or remembering it, our free VBA Developer Kit can help. It’s loaded with VBA shortcuts to help you make your own macros like this one - we’ll send a copy, along with our Big Book of Excel VBA Macros, to your email address below.

I'll take a free VBA Developer Kit

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, "C:\Users\MHS\Documents", is the path to the main search folder. From here, the procedure will traverse all the files in the root of and in all the subfolders of this folder. It drills down in all the folders and subfolders using the VBA GetFolder method of the FileSystemObject.

The second parameter, Array ("xlsm", "xlsb") specifies the types of files you’re looking for. In this example, we’re looking for Excel Macro-Enabled Workbooks and Excel Binary Workbooks, but you can specify exactly the file types you want and practically as many of them as you want. You don’t need to put the period (.) in the front when listing your extensions.

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: Call YourProcedureName(Fil.path).

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

with

If Not IsError(Application.Match(FileExt, FileTypes, 0)) And Not Left(Fil.Name, 1) = "~" Then

in the FindFilesInFolders procedure.

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!

I hope you enjoyed this tutorial. When you’re ready to take your VBA to the next level, subscribe using the form below.