Skip to main content

Excel Macro: List All Files in Folders and Subfolders

This Excel macro can list files (include hidden files) in a specified folder and subfolders, or files with a specific extension.

List All Files in Folders and Subfolders

'-------------- Modules --------------
Sub ListFile(Optional PathSpec As String, Optional FileType As String)
    'Description: List all files in folder and sub-folders (include hidden ,read only...)
    'Web Site: https://excelbaby.com
    'Url: https://excelbaby.com/learn/excel-macro-list-all-files-in-folders-and-subfolders/
    'PathSpec: eg. "D:\MyWork\". if omitted or not exist, Excel will open Browse for Folder to select a folder
    'FileType: Optional, eg. "pdf", "XLSX". if omitted, list all files.

    Dim fso As Object, objshell As Object, objFolder As Object
    Dim oFolder As Object, oSubfolder As Object, oFile As Object, queue As Collection
    Dim LastBlankCell As Integer, Mysheet As Worksheet, F As Boolean, FileExtension As String
    
    '------------------ Select folder ------------------
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set queue = New Collection
    If (PathSpec <> "" And (Dir(PathSpec) <> "" Or Dir(PathSpec & "\") <> "")) Then
        queue.Add fso.GetFolder(PathSpec) 'enqueue
    Else
        Set objshell = CreateObject("Shell.Application")
        Set objFolder = objshell.BrowseForFolder(0, "Select Folder", 0, 0)
        If Not objFolder Is Nothing Then
            queue.Add fso.GetFolder(objFolder.self.Path) 'enqueue
        Else
            Exit Sub
        End If
        Set objFolder = Nothing
        Set objshell = Nothing
    End If
    Application.ScreenUpdating = False 'Disable Screen Updating to speed up macro
    
    '------------------ Add Files sheet ------------------
    'Add a worksheet with name "Files"
    
    For Each Mysheet In ThisWorkbook.Worksheets
        If Mysheet.Name = "Files" Then
            Sheets("Files").Cells.Delete
            F = True
            Exit For
        Else
            F = False
        End If
    Next
    If Not F Then Sheets.Add.Name = "Files"
    'Add table header
    With Sheets("Files")
        .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
        
    '------------------ Core code ------------------
    FileType = UCase(FileType)
    
    Do While queue.Count > 0
        Set oFolder = queue(1)
        queue.Remove 1 'dequeue
        For Each oSubfolder In oFolder.SubFolders
            queue.Add oSubfolder 'enqueue
            '...insert any folder processing code here...
        Next oSubfolder
        LastBlankCell = Cells(Rows.Count, 1).End(xlUp).Row + 1 'get the last blank cell of column A
        For Each oFile In oFolder.Files
            FileExtension = UCase(Split(oFile.Name, ".")(UBound(Split(oFile.Name, ".")))) 'get file extension, eg: TXT
            If (FileType = "" Or FileExtension = FileType) Then
                With Sheets("Files")
                    .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
    Application.ScreenUpdating = True
End Sub

How to use this macro

List all files in select folder

Call ListFile("", "")

List "TXT" files in select folder

Call ListFile("", "txt")

List all files in folder "d:\TestFolder\"

Call ListFile("d:\TestFolder\", "")

or:

Call ListFile("d:\TestFolder", "")

List all PDF files in folder "d:\TestFolder\"

Call ListFile("d:\TestFolder\", "pdf")

Folders property

If you want to set folders property (hidden or unhidden folders), insert any folder processing code in line 65.

Set folders attribute to Unhidden

            If (oSubfolder.Attributes And 2) = 2 Then
                oSubfolder.Attributes = oSubfolder.Attributes - 2
            End If

Set folders attribute to Hidden

            If (oSubfolder.Attributes And 2) <> 2 Then
                oSubfolder.Attributes = oSubfolder.Attributes + 2
            End If

File Property

Determine if the file attribute is Not Hidden

If (oFile.Attributes And 2) <> 2 Then 'The file attribute is Not hidden

Determine if the file attribute is Read only

If (oFile.Attributes And 1) = 1 Then 'The file attribute is Read only

Download

Download this macro and examples.

Learn more

Most VBA code should be placed in Standard Modules unless specified.

If you see a comment '------------------ Modules------------------ in the code header that means put the code in a Standard Module. For more information, learn this course: Where should I put the Excel VBA code?

The following steps teach you how to put VBA code into a Standard Module:

  1. Activate the Visual Basic Editor by pressing ALT + F11.
  2. Right-click the project/workbook name in the Project Window.
  3. Choose Insert -> Module.
  4. Type or paste the code in the newly created module. You will probably need to change the sheet name, the range address, and the save location.
  5. Click Run button on the Visual Basic Editor toolbar.
  6. For more information, learn this course: Programming with Excel VBA

Leave a comment

Your email address will not be published. Required fields are marked *

Format your code: <pre><code class="language-vba">place your code here</code></pre>

2 comments
  1. LE
    Lee

    I have tried it but there is an error with it at line 67

            For Each oSubfolder In oFolder.SubFolders
                queue.Add oSubfolder 'enqueue
                '...insert any folder processing code here...
            Next oSubfolder

    Pls fix it!

    • EX

      @Lee Sorry, I don't understand your question, you can download the whole example test.
      Based on the code you provided, you don't need to insert any folder manipulation code in this example.