How to Use Excel VBA to List Files in Folder

This is an overview.

How to Launch VBA Editor in Excel

Go to the Developer tab on the ribbon or enable the Developer tab on your ribbon..
Select Visual Basic.

In the new window:

Select Insert,
Choose Module.
A new Module will be created.

The list of files will be generated in this folder.

Method 1 – Using FileSystemObject to create a List of Files in a Folder

This is the sample dataset.

To create a list of files, run the following VBA code.

Code Syntax:

'1.Using FileSystemObject
Sub ListFiles_1()
Dim Ob_FSO As Object
Dim Ob_Folder As Object
Dim Ob_File As Object
Dim i As Integer
    Dim Selected_Folder As String
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select a folder for creating list of files"
        .Show
        If .SelectedItems.Count > 0 Then
            Selected_Folder = .SelectedItems(1)
        Else
        End If
    End With
Set Ob_FSO = CreateObject("Scripting.FileSystemObject")
Set Ob_Folder = Ob_FSO.GetFolder(Selected_Folder)
For Each Ob_File In Ob_Folder.Files
    Cells(i + 4, 2) = Ob_File.Name
    i = i + 1
Next Ob_File
End Sub

Code Breakdowm

With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select a folder for creating list of files"
        .Show
        If .SelectedItems.Count > 0 Then
            Selected_Folder = .SelectedItems(1)
        Else
        End If
    End With

The File Explorer selects the folder. The address of that folder is assigned as String to Selected_Folder. If the user selects multiple folders, only the first selection will be taken using the If statement.

Set Ob_FSO = CreateObject("Scripting.FileSystemObject")
Set Ob_Folder = Ob_FSO.GetFolder(Selected_Folder)

A new FileSystemObejct was created and assigned to Ob_FSO. The .GetFolder method of Ob_FSO assigns the Selected_Folder to Ob_Folder.

For Each Ob_File In Ob_Folder.Files
    Cells(i + 4, 2) = Ob_File.Name
    i = i + 1
Next Ob_File

The Ob_Folder.Files property is used to loop through each file in the Selected_Folder using For Each loop and enter the name of each file in the active worksheet starting from B4.

Steps:

Run the code by pressing F5 key or clicking on the Play button.

In the dialog box, select a Folder (Test, here).

A list of all the files will be created.

Note: Files inside subfolders will not be listed.

Method 2 – Using the User-Defined Function to create a List of Files in a Folder

In the user-defined function, the folder address is the argument and returns all file names in the folder. Enter the following code in the VBA code window.

Code Syntax:

Function listfiles(ByVal spath As String)
    Dim va_Array     As Variant
    Dim i           As Integer
    Dim Ob_File       As Object
    Dim Ob_FSO        As Object
    Dim Ob_Folder     As Object
    Dim ob_Files      As Object    
    Set Ob_FSO = CreateObject("Scripting.FileSystemObject")
    Set Ob_Folder = Ob_FSO.GetFolder(spath)
    Set ob_Files = Ob_Folder.Files
    If ob_Files.Count = 0 Then Exit Function
    ReDim va_Array(1 To ob_Files.Count)
    i = 1
    For Each Ob_File In ob_Files
        va_Array(i) = Ob_File.Name
        i = i + 1
    Next
    listfiles = WorksheetFunction.Transpose(va_Array)
End Function

Code Breakdown

  Set Ob_FSO = CreateObject("Scripting.FileSystemObject")
  Set Ob_Folder = Ob_FSO.GetFolder(spath)
  Set ob_Files = Ob_Folder.Files

A new FileSystemObejct was created and assigned to Ob_FSO. The .GetFolder method of Ob_FSO assigns the spath to Ob_Folder. All files in that folder are assigned to the ob_Files object.

For Each Ob_File In ob_Files
        va_Array(i) = Ob_File.Name
        i = i + 1
    Next

All file names are stored in an array (va_Array), using the For Each loop.

listfiles = WorksheetFunction.Transpose(va_Array)

The function listfiles returns the transposed array of va_array.

Enter the following formula to extract all the files in E:\study\Office\Article 72 List Files\Test Folder.

=listfiles(“E:\study\Office\Article 72 List Files\Test Folder”)

Press enter to see the output.

Note: The file address must be inside quotation marks in the formula.

Method 3 – Using FileSystemObject to create a List of Files in a Folder in an Immediate Window

A list of files inside a folder can be displayed in an Immediate Window. Use the following code.

Code Syntax:

' 3.Using FileSystmeObject
'Printing in Immediate Window
Public Sub ListFiles_ImWin()
    Dim spath As String
    Dim i As Integer
    Dim Selected_Folder As String
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select a folder"
        .Show
        If .SelectedItems.Count > 0 Then
            Selected_Folder = .SelectedItems(1)
        Else
        End If
    End With
    Dim Ob_FSO As Object
    Dim Ob_Folder As Object
    Dim Ob_File As Object
    Set Ob_FSO = CreateObject("Scripting.FileSystemObject")
    Set Ob_Folder = Ob_FSO.GetFolder(Selected_Folder)
    For Each Ob_File In Ob_Folder.Files
        Debug.Print Ob_File.Name
    Next Ob_File
    Set Ob_File = Nothing
    Set Ob_Folder = Nothing
    Set Ob_FSO = Nothing
End Sub

Code Breakdown

The code is very similar to the one used in Method 1. The only difference is that here we are displaying the names of all files inside a folder in an Immediate Window, using Debug.Print.

Steps:

Press Ctrl+G or go to View >> Immediate Window.

The immediate window will be displayed.

Run the code by pressing F5. In the new window, choose the folder whose files you want to list in the immediate window.Test Folder, here.
Click OK.

All file names in the folder will be displayed in the Immediate window.

Method 4 – Using Dir Function to create List of Files in a Folder

The Dir function returns a string type value that represents a file name with specific patterns. It can take wildcard characters as the argument. The VBA code below contains the Dir function..

Code Syntax:

'4. Using Dir Function
'Printing in Immediate window
Public Sub ListFilesDir()
    Dim Selected_Folder As String
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select a folder"
        .Show
        If .SelectedItems.Count > 0 Then
            Selected_Folder = .SelectedItems(1)
        Else
        End If
    End With
    Dim sFile As String
    If Right(Selected_Folder, 1) <> "\" Then
        Selected_Folder = Selected_Folder & "\"
    End If
    If sFilter = "" Then
        sFilter = "*.*"
    End If
    'call with path "initializes" the dir function and returns the first file name
    sFile = Dir(Selected_Folder & sFilter)
    i = 4 'Start Writing file names from 4th row
   'call it again until there are no more files
    Do Until sFile = ""
        Cells(i, 2) = sFile
        i = i + 1
        'subsequent calls without parameter return next file name
        sFile = Dir
    Loop
End Sub

Code Breakdown

    Dim Selected_Folder As String
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select a folder"
        .Show
        If .SelectedItems.Count > 0 Then
            Selected_Folder = .SelectedItems(1)
        Else
        End If
    End With

opens the File dialog and asks the user to select the folder. The selected folder’s address is stored in a string-type variable named Selected_Folder.

    If Right(Selected_Folder, 1) <> "\" Then
        Selected_Folder = Selected_Folder & "\"
    End If

adds a backslash to the file address of the folder(Selected_Folder).

   If sFilter = "" Then
        sFilter = "*.*"
    End If

a string type variable sFilter is used and a string with wildcards containing filename pattern(“*.*”)is assigned.

sFile = Dir(Selected_Folder & sFilter)

Dir(Selected_Folder & sFilter) returns the first file name of the Selected_Folder

A new string type variable sFile is used and the return value of the Dir function is assigned to the sFile variable.

 Do Until sFile = ""
        Cells(i, 2) = sFile
        i = i + 1
        'subsequent calls without parameter return next file name
        sFile = Dir
    Loop

a Do Until loop is applied to extract all file names in the Selected_Folder and enter them in the worksheet starting from B4.

Run the code by clicking F5.

In the new window, select a folder to extract the file names.

The list of files in the worksheet will be displayed in the selected folder.

How to List All Files in a Folder with a Specific Extension with Excel VBA

Change this code line,

sFilter = "*.*"

Replace the last * with a file extension such as xlsx, jpg, png, pdf, etc.

sFilter = "*.png"

This will be the new code.

Code Syntax:

Public Sub List_SpecificFiles()
    Dim Selected_Folder As String
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select a folder"
        .Show
        If .SelectedItems.Count > 0 Then
            Selected_Folder = .SelectedItems(1)
        Else
        End If
    End With
    Dim sFile As String
    If Right(Selected_Folder, 1) <> "\" Then
        Selected_Folder = Selected_Folder & "\"
    End If
    If sFilter = "" Then
        sFilter = "*.png" ' type your desired file extension here
    End If
    sFile = Dir(Selected_Folder & sFilter)
    i = 4
    Do Until sFile = ""
        Cells(i, 2) = sFile
        i = i + 1
        sFile = Dir
    Loop
End Sub

Code Breakdown

Check Code Breakdown in Method 4.

Run the code and select the Target folder.

Only png files are listed.

How to List All Files in a Folder & Its SubFolders with Excel VBA

In the example below there is the Test Folder, with a total of 6 Items, and a subfolder (Folder1) with 5 Items.

Use the following VBA Code.

Code Syntax

Sub ListFilesSubFolder()
    Dim Obj_FSO As Object
    Dim Obj_fld As Object
    Dim sf As Object
    Dim file As Object
    Dim folderPath As String
    Dim row As Long
    Set Obj_FSO = CreateObject("Scripting.FileSystemObject")
    Set Obj_fld = Obj_FSO.getFolder(GetFolderPath)
    folderPath = Obj_fld.Path
    row = 4
    For Each file In Obj_FSO.getFolder(folderPath).Files
        Cells(row, 2) = file.Name
        row = row + 1
    Next file
    For Each sf In Obj_FSO.getFolder(folderPath).SubFolders
        ListSubFolderFiles sf, row
    Next sf
End Sub
Sub ListSubFolderFiles(ByRef subFolder As Object, ByRef row As Long)
    Dim Obj_FSO As Object
    Dim file As Object
    Set Obj_FSO = CreateObject("Scripting.FileSystemObject")
    For Each file In subFolder.Files
        Cells(row, 2) = file.Name
        row = row + 1
    Next file
    For Each subFolder In subFolder.SubFolders
        ListSubFolderFiles subFolder, row
    Next subFolder
End Sub
Function GetFolderPath() As String
    Dim fldr As FileDialog
    Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
    With fldr
        .Title = "Select a Folder"
        .AllowMultiSelect = False
        If .Show <> -1 Then Exit Function
        GetFolderPath = .SelectedItems(1)
    End With
End Function

Code Breakdown

Function GetFolderPath() As String
    Dim fldr As FileDialog
    Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
    With fldr
        .Title = "Select a Folder"
        .AllowMultiSelect = False
        If .Show <> -1 Then Exit Function
        GetFolderPath = .SelectedItems(1)
    End With
End Function

prompts a window to select a folder.

Sub ListSubFolderFiles(ByRef subFolder As Object, ByRef row As Long)
    Dim Obj_FSO As Object
    Dim file As Object
    Set Obj_FSO = CreateObject("Scripting.FileSystemObject")
    For Each file In subFolder.Files
        Cells(row, 2) = file.Name
        row = row + 1
    Next file
    For Each subFolder In subFolder.SubFolders
        ListSubFolderFiles subFolder, row
    Next subFolder
End Sub

enters the name of all files in the folder and subfolder in the worksheet.

Sub ListFilesSubFolder()
    Dim Obj_FSO As Object
    Dim Obj_fld As Object
    Dim sf As Object
    Dim file As Object
    Dim folderPath As String
    Dim row As Long
    Set Obj_FSO = CreateObject("Scripting.FileSystemObject")
    Set Obj_fld = Obj_FSO.getFolder(GetFolderPath)
    folderPath = Obj_fld.Path
    row = 4
    For Each file In Obj_FSO.getFolder(folderPath).Files
        Cells(row, 2) = file.Name
        row = row + 1
    Next file
    For Each sf In Obj_FSO.getFolder(folderPath).SubFolders
        ListSubFolderFiles sf, row
    Next sf
End Sub

calls another subroutine ListSubFolderFiles to extract file names inside all subfolders and the folder.

the first subroutine ListFilesSubFolder was run and the Test Folder was selected. This is the output.

How to List All Files in Folders & SubFolders Including File Details with Excel VBA

To list the names of files in a folder and its subfolders but also the details, such as Path, folder, File Extension, Data Created, Last Accessed, Last Modified, Size, and Hidden Status, use the following code.

Code Syntax:

‘List All Files in Folders & SubFolders Including File Details
Option Explicit
Sub ListFilewithDetails()
    Dim Path_Spec As String
    Path_Spec = ""   'Specify a folder
    If (Path_Spec = "") Then Path_Spec = SelectSingleFolder   'Browse for Folder to select a folder
    Dim Ob_FSO As Object
    Set Ob_FSO = CreateObject("Scripting.FileSystemObject")    'Late Binding
    If (Ob_FSO.FolderExists(Path_Spec) = False) Then Exit Sub   'folder exist or not?
    Application.ScreenUpdating = False 'Disable Screen Updating to speed up macro
    Dim My_Sheet_Name As String
    My_Sheet_Name = "Files"   'Add a Sheet with name "Files"
    Add_Sheet (My_Sheet_Name)
    Dim File_Type As String
    File_Type = "*"   '*:all, or pdf, PDF, XLSX...
    File_Type = UCase(File_Type)
    Dim Coll_queue As Collection, oFolder As Object, oSubfolder As Object, oFile As Object
    Dim LastBlankCell As Long, FileExtension As String
    Set Coll_queue = New Collection
    Coll_queue.Add Ob_FSO.getFolder(Path_Spec) 'enqueue
    Do While Coll_queue.Count > 0
        Coll_queue.Remove 1 'dequeue  
        For Each oSubfolder In oFolder.SubFolders   'loop all sub-folders
            Coll_queue.Add oSubfolder 'enqueue
            '...insert any folder processing code here...
        Next oSubfolder  
        LastBlankCell = ThisWorkbook.Sheets(My_Sheet_Name).Cells(Rows.Count, 1).End(xlUp).row + 1 'get the last blank cell of column A
        For Each oFile In oFolder.Files 'loop all files
            FileExtension = UCase(Split(oFile.Name, ".")(UBound(Split(oFile.Name, ".")))) 'get file extension, eg: TXT
            If (File_Type = "*" Or FileExtension = File_Type) Then
                With ThisWorkbook.Sheets(My_Sheet_Name)
                    .Cells(LastBlankCell, 1) = oFile 'Path
                    .Cells(LastBlankCell, 2) = oFolder 'Folder
                    .Cells(LastBlankCell, 3) = oFile.Name 'File Name
                    .Cells(LastBlankCell, 4) = FileExtension 'File Extension
                    .Cells(LastBlankCell, 5) = oFile.DateCreated 'Data Created
                    .Cells(LastBlankCell, 6) = oFile.DateLastAccessed 'Last Accessed
                    .Cells(LastBlankCell, 7) = oFile.DateLastModified 'Last Modified
                    .Cells(LastBlankCell, 8) = oFile.Size 'File Size
                    If (oFile.Attributes And 2) = 2 Then
                        .Cells(LastBlankCell, 9) = "TRUE" 'Is Hidden
                     Else
                        .Cells(LastBlankCell, 9) = "FALSE" 'Is Hidden
                    End If
                End With
                LastBlankCell = LastBlankCell + 1
            End If
        Next oFile
    Loop    
    'Cells.EntireColumn.AutoFit  'Autofit columns width
    Application.ScreenUpdating = True
End Sub
Function SelectSingleFolder()
    'Select a Folder Path    
    Dim FolderPicker As FileDialog
    Dim myFolder As String   
    'Select Folder with Dialog Box
    Set FolderPicker = Application.FileDialog(msoFileDialogFolderPicker) 
    With FolderPicker
        .Title = "Select A Single Folder"
        .AllowMultiSelect = False
        If .Show <> -1 Then Exit Function 'Check if user clicked cancel button
        SelectSingleFolder = .SelectedItems(1)
    End With
End Function
Function Add_Sheet(My_Sheet_Name As String)
    'Add a worksheet with custom name   
    Dim Mysheet As Worksheet, F As Boolean
    For Each Mysheet In ThisWorkbook.Worksheets
        If Mysheet.Name = My_Sheet_Name Then
            Sheets(My_Sheet_Name).Cells.Delete
            F = True
            Exit For
        Else
            F = False
        End If
    Next
    If Not F Then Sheets.Add.Name = My_Sheet_Name  
    'Add table header
    With Sheets(My_Sheet_Name)
        .Cells(1, 1) = "Path"
        .Cells(1, 2) = "Folder"
        .Cells(1, 3) = "File Name"
        .Cells(1, 4) = "File Extension"
        .Cells(1, 5) = "Data Created"
        .Cells(1, 6) = "Last Accessed"
        .Cells(1, 7) = "Last Modified"
        .Cells(1, 8) = "Size"
        .Cells(1, 9) = "Is Hidden"
    End With
End Function

Code Breakdown

Function Add_Sheet(My_Sheet_Name As String)
    'Add a worksheet with custom name
    Dim Mysheet As Worksheet, F As Boolean
    For Each Mysheet In ThisWorkbook.Worksheets
        If Mysheet.Name = My_Sheet_Name Then
            Sheets(My_Sheet_Name).Cells.Delete
            F = True
            Exit For
        Else
            F = False
        End If
    Next
    If Not F Then Sheets.Add.Name = My_Sheet_Name   
    'Add table header
    With Sheets(My_Sheet_Name)
        .Cells(1, 1) = "Path"
        .Cells(1, 2) = "Folder"
        .Cells(1, 3) = "File Name"
        .Cells(1, 4) = "File Extension"
        .Cells(1, 5) = "Data Created"
        .Cells(1, 6) = "Last Accessed"
        .Cells(1, 7) = "Last Modified"
        .Cells(1, 8) = "Size"
        .Cells(1, 9) = "Is Hidden"
    End With
End Function

creates a new sheet; enter the titles of the columns and the properties of the files.

Function SelectSingleFolder()
    'Select a Folder Path
    Dim FolderPicker As FileDialog
    Dim myFolder As String
    'Select Folder with Dialog Box
    Set FolderPicker = Application.FileDialog(msoFileDialogFolderPicker)
    With FolderPicker
        .Title = "Select A Single Folder"
        .AllowMultiSelect = False
        If .Show <> -1 Then Exit Function 'Check if user clicked cancel button
        SelectSingleFolder = .SelectedItems(1)
    End With
End Function

prompts the user to select a folder.

Sub ListFilewithDetails()
    Dim Path_Spec As String
    Path_Spec = ""   'Specify a folder
    If (Path_Spec = "") Then Path_Spec = SelectSingleFolder   'Browse for Folder to select a folder
 My_Sheet_Name = "Files"   'Add a Sheet with name "Files"
  Add_Sheet (My_Sheet_Name)
 'Cells.EntireColumn.AutoFit  'Autofit columns width
    Application.ScreenUpdating = True
End Sub

calls the previous two functions to select a single folder and create a new worksheet with a custom name. It extracts information of each file and enters it in the new sheet.

Run this code.

In the new window, select a folder. A new worksheet (Files) will be created and all details will be listed.