How To List All Files In Folder And Sub-folders Use Excel VBA

The following Excel VBA code can help you to list all files in folder and sub-folders into a worksheet.

Final update: Please read this article: Excel Macro: List All Files in Folders and Subfolders

VBA Code

Sub ListAllFilesInAllFolders()

    Dim MyPath As String, MyFolderName As String, MyFileName As String, key As Variant
    Dim i As Integer, F As Boolean
    Dim objShell As Object, objFolder As Object, AllFolders As Object, AllFiles As Object
    Dim MySheet As Worksheet
    On Error Resume Next
    'Select folder
    Set objShell = CreateObject("Shell.Application")
    Set objFolder = objShell.BrowseForFolder(0, "", 0, 0)
    If Not objFolder Is Nothing Then
        MyPath = objFolder.self.Path & "\"
        Exit Sub
       'MyPath = "G:\BackUp\"
    End If
    Set objFolder = Nothing
    Set objShell = Nothing
    'List all folders
    Set AllFolders = CreateObject("Scripting.Dictionary")
    Set AllFiles = CreateObject("Scripting.Dictionary")
    AllFolders.Add (MyPath), ""
    i = 0
    Do While i < AllFolders.Count
        Key = AllFolders.keys
        MyFolderName = Dir(Key(i), vbDirectory)
        Do While MyFolderName <> ""
            If MyFolderName <> "." And MyFolderName <> ".." Then
                If (GetAttr(Key(i) & MyFolderName) And vbDirectory) = vbDirectory Then
                    AllFolders.Add (Key(i) & MyFolderName & "\"), ""
                End If
            End If
            MyFolderName = Dir
        i = i + 1
    'List all files
    For Each Key In AllFolders.keys
        MyFileName = Dir(Key & "*.*")
        'MyFileName = Dir(Key & "*.PDF")    'only PDF files
        Do While MyFileName <> ""
            AllFiles.Add (Key & MyFileName), ""
            MyFileName = Dir
    'List all files in Files sheet
    For Each MySheet In ThisWorkbook.Worksheets
        If MySheet.Name = "Files" Then
            F = True
            Exit For
            F = False
        End If
    If Not F Then Sheets.Add.Name = "Files"

    'Sheets("Files").[A1].Resize(AllFolders.Count, 1) = WorksheetFunction.Transpose(AllFolders.keys)
    Sheets("Files").[A1].Resize(AllFiles.Count, 1) = WorksheetFunction.Transpose(AllFiles.keys)
    Set AllFolders = Nothing
    Set AllFiles = Nothing
End Sub

Cloumn A: Directory, Cloumn B: File Name

Line 49

AllFiles.Add (Key & MyFileName), ""


AllFiles.Add (MyFileName), Key

Line 69

Sheets("Files").[A1].Resize(AllFiles.Count, 1) = WorksheetFunction.Transpose(AllFiles.keys)


Sheets("Files").[A1].Resize(AllFiles.Count, 1) = WorksheetFunction.Transpose(AllFiles.Items)
Sheets("Files").[B1].Resize(AllFiles.Count, 1) = WorksheetFunction.Transpose(AllFiles.keys)

Others Useful Code

Get File Date

Do While MyFileName <> ""
    DateStamp = FileDateTime(key & MyFileName)
    AllFiles.Add (key & MyFileName), DateStamp
    MyFileName = Dir

Get File Size

Do While MyFileName <> ""
    MyFileSize = FileLen(key & MyFileName)
    AllFiles.Add (MyFileName), MyFileSize
    MyFileName = Dir

How to Use This Macro

To use this macro, you can copy and paste it 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.
    Insert Module
  4. Type or paste the code in the newly created module.
  5. Close the VBE widow.
  6. Select the range which you want to remove duplicate values.
  7. On the Developer tab, in the Code group, click Macros.
  8. Select the macro which you want to run, in this case we select GetUniqueValues, then click Run.

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>

  1. GA

    Thank you so much! You save me!

More comments