How to get a list of all excel files in a folder and subfolders with VBA

 In the daily running of business processes it is common to have files saved in different folders and subfolders according to rules set to organize the information 

As an example you can have a main folder called "Customers" and several subfolders named with the customer name. In each subfolder you can then have different files such as excel with the purchase history, PDF files with the invoices and word files with the last offer presented.

There are often instances in which business requires to have ONE information on ALL customers instead of ALL information on ONE customer

If we would like to know which customers have a purchase history ( stored in the excel file), we would be asking a question that can not be answered easily if we have a folder hierarchy as the one just described. 

The below picture provides a snapshot of the challenge we would be facing 

In order to answer the query, the first step is to identify which customer folders contain an excel file 

Luckily VBA has the solution on how to get a list of all excel files in a folder and subfolders

The VBA code explained in this post will allow us to have a final list of all excel files  in different subfolders located under a main folder

The code will go through the following key steps:

1. Locate the main folder

2. Identify all the subfolders and loop through them 

3. Determine if the subfolder contains an excel file

4. Create a list with all the paths of all the excel files found

THIS POST explains how VBA capabilities in locating excel files can be leveraged to pull together data reported across different workbooks

The code starst by setting the variables that will be used

Cell C1 of the active sheet, contains the filepath to the main folder 

The FileSystem Object will allow to access the main folder.

The FileSystem Object allows to access all folders on the pc, and perform specific actions such at creating and writing TXT files. 

THIS POST explains how to create TXT files using the FileSystem object.It is important to know that the FileSystem object works only on Windows PCs.

The variables are set 

Dim fs, f, f1, s, sf

    Dim fpath As String

The path to the main folder is reported in cell C1 of the activesheet

    fpath = Range("c1").Value

The FileSystem object is created enabling the code to access the folders on the PC

    Set fs = CreateObject("Scripting.FileSystemObject")

The GetFolder property allows to identify a specific folder. 

The path of the main folder is provided to the GetFolder method allowing to identify the main folder

    Set f = fs.GetFolder(fpath)

The GetFolder method of the FileSystem object returns the object represented by the main folder

Applying the .SubFolders method to the main folder returns a collection of all the SubFolders 

    Set sf = f.SubFolders

Next step is to loop through all the subfolders and loop through all the files placed in each subfolder

In order to do so a double loop is executed 

The variable i is set to 2. It will be used to determine the row where to copy the file path

 i = 2

The first loop starts looping through all the subfolders

    For Each sb In sf

A second loop goes through all the files  contained in each subfolder. It uses the .Files property applied to the subfolders in order to return a collection of files 

        For Each f In sb.Files

Each file contained in the subfolder is analysed to understand if its file path contains the string ".xl"

The ".xl" has been selected as the identifier of an excel file because when naming a file, the dot can be used only at the start of the file extension, and the "xl" string is common to all excel files.

Additionally the wild character * has been used before and after the ".xl" string. This will allow to identify any excel file regardless of the length of its name and the type of extension (xlsx, xlsm, xlsb, xls,...)

If the file contains the ".xl" string the full file path is copied to column A and row i of the activesheet

If f.Path Like "*.xl*" Then

        Cells(i, 1).Value = f.Path

        i = i + 1

        Else: End If

The second loop moves on to the next item and when it reaches the bottom then the first loop moves forward to the next item

Next 

Next 

End Sub

The full code is reported below for convenience

Sub searchfileinfolder()

    Dim fs, f, f1, s, sf

    Dim fpath As String

    fpath = Range("c1").Value

    Set fs = CreateObject("Scripting.FileSystemObject")

    Set f = fs.GetFolder(fpath)

    Set sf = f.SubFolders

    i = 2

    For Each sb In sf

        For Each f In sb.Files

        If f.Path Like "*.xl*" Then

        Cells(i, 1).Value = f.Path

        i = i + 1

        Else: End If

       Next

    Next

End Sub