Home
How to List All Files from Subfolders with EXCEL VBA Easily

How to List All Files from Subfolders with EXCEL VBA Easily

Excel VBA Macros December 26, 2022

Welcome to Excel Avon

List All Files from Subfolders

DOWNLOAD USED EXCEL FILE>>

In today’s post, we will explain you how to List All Files from Subfolder using Excel VBA, although you can manually list all files from subfolders, but when you need more data If you work together, it can take a long time. List All Files from Subfolders, we can also use VBA to List All Files from Subfolders, it will take a lot of time no matter how much data is there. VBA allows you to List all files from subfolders of a folder using the FileSystemObject. 

As you can see in the image below, we have multiple subfolders containing files which we will list together. List all files inside these subfolders.

List-All-Files from-Subfolders-with-EXCEL-VBA

We will now activate Microsoft Script Runtime Library, to activate go to Tools option and then References and on that we select Microsoft Script Runtime Library and click OK.

List-all-files-inside-a-folder.1

How to List All Files from Subfolders VBA

Well, you might know how to do all the listing manually but how we do it in VBA, we will learn in this article. Similarly, it is easy to do in VBE also, let’s understand. Through this post, we will know How to List all files from Subfolder with the help of VBA, then we have to go like last time, first go to the Developer Tab, then click on the option of Visual Basic as shown in the image below.

sort-data-on-excel-using-VBA

On opening in VBE, you have to go to Insert and then Module has to be inserted, as can be seen in the image.

Sort-data-on-excel-using-vba

Once we insert the module, we will write a subroutine for List All Files from Subfolders.

Sub ListFiles(FolPath as String)

End Sub

Declaring variables which are using in the entire project for Filesystem Object, File, worksheet, Long and Folder.

Sub ListFiles(FolPath As String)
Dim FSO As New FileSystemObject
Dim aFile As File
Dim WS As Worksheet
Dim Lr As Long
Dim subFol As Folder

End Sub

Now we will set WS as ActiveSeet.

Sub ListFiles(FolPath As String)
Dim FSO As New FileSystemObject
Dim aFile As File
Dim WS As Worksheet
Dim Lr As Long
Dim subFol As Folder

Set WS = ActiveSheet
End Sub

‘For Each’ signifies that for each entity in an array or the range repeat the process in for loop. We will use the ‘next’ keyword to move forward in the range. For range in loop, we will use FileSystemObject.

Sub ListFiles(FolPath As String)
Dim FSO As New FileSystemObject
Dim aFile As File
Dim WS As Worksheet
Dim Lr As Long
Dim subFol As Folder

Set WS = ActiveSheet

For Each aFile In FSO.GetFolder(FolPath).Files
Next aFile
End Sub

Now we will declare the last row. 

Sub ListFiles(FolPath As String)
Dim FSO As New FileSystemObject
Dim aFile As File
Dim WS As Worksheet
Dim Lr As Long
Dim subFol As Folder

Set WS = ActiveSheet

For Each aFile In FSO.GetFolder(FolPath).Files
    Lr = WS.Range("A" & Rows.Count).End(xlUp).Row + 1
Next aFile
End Sub

We have separated columns for files attributes, whose range we have to write with the worksheet.

Sub ListFiles(FolPath As String)
Dim FSO As New FileSystemObject
Dim aFile As File
Dim WS As Worksheet
Dim Lr As Long
Dim subFol As Folder

Set WS = ActiveSheet
For Each aFile In FSO.GetFolder(FolPath).Files
    Lr = WS.Range("A" & Rows.Count).End(xlUp).Row + 1
    WS.Range("A" & Lr).Value = aFile.Name
Next aFile
End Sub

Now we will copy and paste the code that we have written for cell range and file attributes and change the cell range and files attributes when you paste code. As we have bolded the changed attributes.

Sub ListFiles(FolPath As String)
Dim FSO As New FileSystemObject
Dim aFile As File
Dim WS As Worksheet
Dim Lr As Long
Dim subFol As Folder

Set WS = ActiveSheet

For Each aFile In FSO.GetFolder(FolPath).Files
Lr = WS.Range("A" & Rows.Count).End(xlUp).Row + 1
WS.Range("A" & Lr).Value = aFile.Name
WS.Range("B" & Lr).Value = aFile.Type
WS.Range("C" & Lr).Value = aFile.Path
WS.Range("D" & Lr).Value = aFile.Size
WS.Range("E" & Lr).Value = aFile.DateCreated
WS.Range("F" & Lr).Value = aFile.DateLastModified
Next aFile
End Sub

Now we will use Each Loop for Subfolder and use path for Subfolder.

Sub ListFiles(FolPath As String)
Dim FSO As New FileSystemObject
Dim aFile As File
Dim WS As Worksheet
Dim Lr As Long
Dim subFol As Folder

Set WS = ActiveSheet

For Each aFile In FSO.GetFolder(FolPath).Files
    Lr = WS.Range("A" & Rows.Count).End(xlUp).Row + 1
    WS.Range("A" & Lr).Value = aFile.Name
    WS.Range("B" & Lr).Value = aFile.Type
    WS.Range("C" & Lr).Value = aFile.Path
    WS.Range("D" & Lr).Value = aFile.Size
    WS.Range("E" & Lr).Value = aFile.DateCreated
    WS.Range("F" & Lr).Value = aFile.DateLastModified
Next aFile

For Each subFol In FSO.GetFolder(FolPath).SubFolders
    ListFiles subFol.Path
Next subFol
End Sub

 We will write another subroutine for List All Files from Subfolders.

Sub ListAllFiles()

End Sub

Sub ListFiles(FolPath As String)
Dim FSO As New FileSystemObject
Dim aFile As File
Dim WS As Worksheet
Dim Lr As Long
Dim subFol As Folder

Set WS = ActiveSheet

For Each aFile In FSO.GetFolder(FolPath).Files
    Lr = WS.Range("A" & Rows.Count).End(xlUp).Row + 1
    WS.Range("A" & Lr).Value = aFile.Name
    WS.Range("B" & Lr).Value = aFile.Type
    WS.Range("C" & Lr).Value = aFile.Path
    WS.Range("D" & Lr).Value = aFile.Size
    WS.Range("E" & Lr).Value = aFile.DateCreated
    WS.Range("F" & Lr).Value = aFile.DateLastModified
Next aFile

For Each subFol In FSO.GetFolder(FolPath).SubFolders
    ListFiles subFol.Path
Next subFol
End Sub

Another variable Define for FileDiolog and string.

Sub ListAllFiles()
Dim FD As FileDialog
Dim FolPath As String

End Sub

Sub ListFiles(FolPath As String)
Dim FSO As New FileSystemObject
Dim aFile As File
Dim WS As Worksheet
Dim Lr As Long
Dim subFol As Folder

Set WS = ActiveSheet

For Each aFile In FSO.GetFolder(FolPath).Files
    Lr = WS.Range("A" & Rows.Count).End(xlUp).Row + 1
    WS.Range("A" & Lr).Value = aFile.Name
    WS.Range("B" & Lr).Value = aFile.Type
    WS.Range("C" & Lr).Value = aFile.Path
    WS.Range("D" & Lr).Value = aFile.Size
    WS.Range("E" & Lr).Value = aFile.DateCreated
    WS.Range("F" & Lr).Value = aFile.DateLastModified
Next aFile

For Each subFol In FSO.GetFolder(FolPath).SubFolders
    ListFiles subFol.Path
Next subFol
End Sub

Now we will create FileDiolog, FileDiolog Select as FolderPicker.

Sub ListAllFiles()
Dim FD As FileDialog
Dim FolPath As String

Set FD = Application.FileDialog(msoFileDialogFolderPicker)
End Sub

Sub ListFiles(FolPath As String)
Dim FSO As New FileSystemObject
Dim aFile As File
Dim WS As Worksheet
Dim Lr As Long
Dim subFol As Folder

Set WS = ActiveSheet

For Each aFile In FSO.GetFolder(FolPath).Files
    Lr = WS.Range("A" & Rows.Count).End(xlUp).Row + 1
    WS.Range("A" & Lr).Value = aFile.Name
    WS.Range("B" & Lr).Value = aFile.Type
    WS.Range("C" & Lr).Value = aFile.Path
    WS.Range("D" & Lr).Value = aFile.Size
    WS.Range("E" & Lr).Value = aFile.DateCreated
    WS.Range("F" & Lr).Value = aFile.DateLastModified
Next aFile

For Each subFol In FSO.GetFolder(FolPath).SubFolders
    ListFiles subFol.Path
Next subFol
End Sub

Now we will work with ‘with‘ Statement. ‘WITH‘ statement is used in VBA to access all the properties and methods of the specified object. Here we will name the title with (Statement) file dialog. And then Give the Button Name. 

Sub ListAllFiles()
Dim FD As FileDialog
Dim FolPath As String

Set FD = Application.FileDialog(msoFileDialogFolderPicker)
With FD
    .Title = "Choose a folder"
    .ButtonName = "Choose"
End with
End Sub

Sub ListFiles(FolPath As String)
Dim FSO As New FileSystemObject
Dim aFile As File
Dim WS As Worksheet
Dim Lr As Long
Dim subFol As Folder

Set WS = ActiveSheet

For Each aFile In FSO.GetFolder(FolPath).Files
    Lr = WS.Range("A" & Rows.Count).End(xlUp).Row + 1
    WS.Range("A" & Lr).Value = aFile.Name
    WS.Range("B" & Lr).Value = aFile.Type
    WS.Range("C" & Lr).Value = aFile.Path
    WS.Range("D" & Lr).Value = aFile.Size
    WS.Range("E" & Lr).Value = aFile.DateCreated
    WS.Range("F" & Lr).Value = aFile.DateLastModified
Next aFile

For Each subFol In FSO.GetFolder(FolPath).SubFolders
    ListFiles subFol.Path
Next subFol
End Sub

Create condition with if statement for show, creating another condition with if statement (If the number of select item is more than 0 even 1). 

Sub ListAllFiles()
Dim FD As FileDialog
Dim FolPath As String

Set FD = Application.FileDialog(msoFileDialogFolderPicker)
With FD
    .Title = "Choose a folder"
    .ButtonName = "Choose"
     If .Show = True Then
        If .SelectedItems.Count > 0 Then
        End If
     End If
End with
End Sub

Sub ListFiles(FolPath As String)
Dim FSO As New FileSystemObject
Dim aFile As File
Dim WS As Worksheet
Dim Lr As Long
Dim subFol As Folder

Set WS = ActiveSheet

For Each aFile In FSO.GetFolder(FolPath).Files
    Lr = WS.Range("A" & Rows.Count).End(xlUp).Row + 1
    WS.Range("A" & Lr).Value = aFile.Name
    WS.Range("B" & Lr).Value = aFile.Type
    WS.Range("C" & Lr).Value = aFile.Path
    WS.Range("D" & Lr).Value = aFile.Size
    WS.Range("E" & Lr).Value = aFile.DateCreated
    WS.Range("F" & Lr).Value = aFile.DateLastModified
Next aFile

For Each subFol In FSO.GetFolder(FolPath).SubFolders
    ListFiles subFol.Path
Next subFol
End Sub

Now create another condition, If the number of Select Item in the FolderPath is 1.

Sub ListAllFiles()
Dim FD As FileDialog
Dim FolPath As String

Set FD = Application.FileDialog(msoFileDialogFolderPicker)
With FD
     .Title = "Choose a folder"
     .ButtonName = "Choose"
     If .Show = True Then
        If .SelectedItems.Count > 0 Then
           FolPath = .SelectedItems(1)
        End If
     End If
End with
End Sub

Sub ListFiles(FolPath As String)
Dim FSO As New FileSystemObject
Dim aFile As File
Dim WS As Worksheet
Dim Lr As Long
Dim subFol As Folder

Set WS = ActiveSheet

For Each aFile In FSO.GetFolder(FolPath).Files
    Lr = WS.Range("A" & Rows.Count).End(xlUp).Row + 1
    WS.Range("A" & Lr).Value = aFile.Name
    WS.Range("B" & Lr).Value = aFile.Type
    WS.Range("C" & Lr).Value = aFile.Path
    WS.Range("D" & Lr).Value = aFile.Size
    WS.Range("E" & Lr).Value = aFile.DateCreated
    WS.Range("F" & Lr).Value = aFile.DateLastModified
Next aFile

For Each subFol In FSO.GetFolder(FolPath).SubFolders
    ListFiles subFol.Path
Next subFol
End Sub

Now create another condition, If the FolderPath is not Blank.

Sub ListAllFiles()
Dim FD As FileDialog
Dim FolPath As String

Set FD = Application.FileDialog(msoFileDialogFolderPicker)
With FD
     .Title = "Choose a folder"
     .ButtonName = "Choose"
     If .Show = True Then
        If .SelectedItems.Count > 0 Then
           FolPath = .SelectedItems(1)
        End If
     End If
End with
If FolPath <> "" Then
   ListFiles Folpath
End If

End Sub

Sub ListFiles(FolPath As String)
Dim FSO As New FileSystemObject
Dim aFile As File
Dim WS As Worksheet
Dim Lr As Long
Dim subFol As Folder

Set WS = ActiveSheet

For Each aFile In FSO.GetFolder(FolPath).Files
    Lr = WS.Range("A" & Rows.Count).End(xlUp).Row + 1
    WS.Range("A" & Lr).Value = aFile.Name
    WS.Range("B" & Lr).Value = aFile.Type
    WS.Range("C" & Lr).Value = aFile.Path
    WS.Range("D" & Lr).Value = aFile.Size
    WS.Range("E" & Lr).Value = aFile.DateCreated
    WS.Range("F" & Lr).Value = aFile.DateLastModified
Next aFile

For Each subFol In FSO.GetFolder(FolPath).SubFolders
    ListFiles subFol.Path
Next subFol
End Sub

Now we will use message box function that we will get message after List All Files from Subfolders.

Sub ListAllFiles()
Dim FD As FileDialog
Dim FolPath As String

Set FD = Application.FileDialog(msoFileDialogFolderPicker)
With FD
     .Title = "Choose a folder"
     .ButtonName = "Choose"
     If .Show = True Then
        If .SelectedItems.Count > 0 Then
           FolPath = .SelectedItems(1)
        End If
     End If
End with
If FolPath <> "" Then
   ListFiles Folpath
   MsgBox "All Files listed successfully!", vbInformation
End If

End Sub

Sub ListFiles(FolPath As String)
Dim FSO As New FileSystemObject
Dim aFile As File
Dim WS As Worksheet
Dim Lr As Long
Dim subFol As Folder

Set WS = ActiveSheet

For Each aFile In FSO.GetFolder(FolPath).Files
    Lr = WS.Range("A" & Rows.Count).End(xlUp).Row + 1
    WS.Range("A" & Lr).Value = aFile.Name
    WS.Range("B" & Lr).Value = aFile.Type
    WS.Range("C" & Lr).Value = aFile.Path
    WS.Range("D" & Lr).Value = aFile.Size
    WS.Range("E" & Lr).Value = aFile.DateCreated
    WS.Range("F" & Lr).Value = aFile.DateLastModified
Next aFile

For Each subFol In FSO.GetFolder(FolPath).SubFolders
    ListFiles subFol.Path
Next subFol
End Sub

When you write the code (List All Files from Subfolders), you have to go to the excel sheet or activate it and there you can insert a button that will be used to run the macro. So, we will go to the insert option and then select the button, drag and select the select the macro name (‘List All Files’) from the available list and click on OK button and you can also name the button.

List-all-files-inside-a-folder2

finally, our button is made. and now by clicking this will list all files from subfolders.

If you click on the button, it will open the file in the folder where you select the folder. and then click on the choose button. You can see the title and button name of the popup window which will be your given name.

List-all-files-inside-a-folder3

We will select a file and click on the Choose button. As soon as you click on the button, you will see List All Files from Subfolders Listed on Excel. All files name, create date, folder path, file type and modified date are filled in Excel.

List-All-Files from-Subfolders-with-EXCEL-VBA 1

So, I hope you have understood How to List All Files from Subfolders with EXCEL VBA and for more information, you can follow us on Twitter, Instagram, LinkedIn, and YouTube as well.

DOWNLOAD USED EXCEL FILE>>

LEARN MORE TOPIC IN VBA HERE

You can also see well-explained video here about How to List All Files from Subfolders with EXCEL VBA

Related Posts

Leave a Reply