Previously in the article Find and List all Files and Folders in a Directory I’ve explained how you can use VBA to find and list all the files and folders in a specific directory. In this article I will explain how you can do the same, only this time you will get all the files in that directory and all its subdirectories. I will be using a recursive call to one my functions in order to implement this.

Get All Files:

When the user presses the “Get Files” button a folder dialog will open asking the user to select a directory:

Upon selecting a directory the name of all the files in that directory and all its sub directories will printed in column A and B:

The code for this program can be seen below:

Option Explicit
'the first row with data
Const ROW_FIRST As Integer = 5 

'This is an event handler. It exectues when the user
'presses the run button
Private Sub btnGet_Click()
'determines if the user selects a directory
'from the folder dialog
Dim intResult As Integer
'the path selected by the user from the
'folder dialog
Dim strPath As String
'Filesystem object
Dim objFSO As Object
'the current number of rows
Dim intCountRows As Integer
Application.FileDialog(msoFileDialogFolderPicker).Title = _
"Select a Path"
'the dialog is displayed to the user
intResult = Application.FileDialog( _
msoFileDialogFolderPicker).Show
'checks if user has cancled the dialog
If intResult <> 0 Then
    strPath = Application.FileDialog(msoFileDialogFolderPicker _
    ).SelectedItems(1)
    'Create an instance of the FileSystemObject
    Set objFSO = CreateObject("Scripting.FileSystemObject")
   
    'loops through each file in the directory and prints their
    'names and path
    intCountRows = GetAllFiles(strPath, ROW_FIRST, objFSO)
    'loops through all the files and folder in the input path
    Call GetAllFolders(strPath, objFSO, intCountRows)
End If
End Sub 

'''
'This function prints the name and path of all the files
'in the directory strPath
'strPath: The path to get the list of files from
'intRow: The current row to start printing the file names
'in
'objFSO: A Scripting.FileSystem object.
Private Function GetAllFiles(ByVal strPath As String, _
ByVal intRow As Integer, ByRef objFSO As Object) As Integer
Dim objFolder As Object
Dim objFile As Object
Dim i As Integer
i = intRow - ROW_FIRST + 1
Set objFolder = objFSO.GetFolder(strPath)
For Each objFile In objFolder.Files
        'print file name
        Cells(i + ROW_FIRST - 1, 1) = objFile.Name
        'print file path
        Cells(i + ROW_FIRST - 1, 2) = objFile.Path
        i = i + 1
Next objFile
GetAllFiles = i + ROW_FIRST - 1
End Function

'''
'This function loops through all the folders in the
'input path. It makes a call to the GetAllFiles
'function. It also makes a recursive call to itself
'strFolder: The folder to loop through
'objFSO: A Scripting.FileSystem object
'intRow: The current row to print the file data on
Private Sub GetAllFolders(ByVal strFolder As String, _
ByRef objFSO As Object, ByRef intRow As Integer)
Dim objFolder As Object
Dim objSubFolder As Object

'Get the folder object
Set objFolder = objFSO.GetFolder(strFolder)
'loops through each file in the directory and
'prints their names and path
For Each objSubFolder In objFolder.subfolders
    intRow = GetAllFiles(objSubFolder.Path, _
        intRow, objFSO)
    'recursive call to to itsself
    Call GetAllFolders(objSubFolder.Path, _
        objFSO, intRow)
Next objSubFolder
End Sub


Code Explanation:

The function below executes when the user presses the “Get File” button:

Private Sub btnGet_Click()
...
End Sub


The next few lines of the code is related to the folder dialog:

Application.FileDialog(msoFileDialogFolderPicker).Title = _
"Select a Path"
'the dialog is displayed to the user
intResult = Application.FileDialog( _
msoFileDialogFolderPicker).Show
'checks if user has cancled the dialog
If intResult <> 0 Then
    'dispaly message box
    strPath = Application.FileDialog(msoFileDialogFolderPicker _
    ).SelectedItems(1)
    ...
End If


Basically it displays a folder dialog, and checks the user action. For more information about using Folder Dialogs please see the article below:

• VBA Folder Dialog

The call to the function GetAllFiles, will print all the files in the directory specified. Note that it returns the index of the last row, so the next call to the function will know which row to start printing the file names:

intCountRows = GetAllFiles(strPath, ROW_FIRST, objFSO)


The function itself has previously been explained in the article below:

• Find and List all Files and Folders in a Directory

The next line of code in the btnGet_Click event handler makes a call to the GetAllFolders function. This function loops through all the folders in the directory passed to it. For each directory it finds it calls the function GetAllFiles for that directory. It then makes a recursive call to itself, passing that folder as an input parameter.

The line below gets the folder object associated with the path passed as an input parameter to the function:

Set objFolder = objFSO.GetFolder(strFolder)


The for each loop below iterates through all the folders in the current folder:

For Each objSubFolder In objFolder.subfolders
...
Next objSubFolder


The call to the function GetAllFiles prints the name of all the files in the current folder:

intCountRows = GetAllFiles(strPath, ROW_FIRST, objFSO)


The line below is a recursive call to the GetAllFolders function.

Call GetAllFolders(strPath, objFSO, intCountRows)


You can download the file and code related to this article from the link below:

• Find and List, In directory and Subdirectories.xlsm

See also:

• Find and List all Files and Folders in a Directory
• VBA Copy File
• VBA Delete File
• VBA Check File Exists
• List All Files in a Folder and Create Hyperlinks to Each File, Excel VBA

If you need assistance with your code, or you are looking for a VBA programmer to hire feel free to contact me. Also please visit my website  www.software-solutions-online.com