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.

VBA Code

Sub ListAllFilesInAllFolders()

    Dim MyPath As String, MyFolderName As String, MyFileName As String
    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 & "\"
    Else
        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
        Loop
        i = i + 1
    Loop
    
    '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
        Loop
    Next
    
    '************************
    'List all files in Files sheet
    
    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"

    '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
Replace

AllFiles.Add (Key & MyFileName), ""

To

AllFiles.Add (MyFileName), Key

Line 69
Replace

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

To

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
Loop

Get File Size

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

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 Reply

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

24 comments
  1. Denisha
    Denisha

    Yup, it’s a dark actuality for anyone that wishes to keep
    up with their Tv reveals from house in a brand new
    nation. Nearly all of Tv network sites and paid subscription services either completely block
    the content from being viewed outdoors of the United States, or the quantity of content material is proscribed.
    I moved from the U.S. Panama virtually four years in the past, and
    during my time here I’d say I have grow to be an knowledgeable in getting around anything that retains me
    from watching the newest episodes of Scandal.
    In Panama, I simply have essentially the most primary cable package deal as a result of I
    want being in a position to observe Tv reveals and films on demand using services like Netflix,
    Hulu, or Amazon. Also, most major networks like Fox, ABC, NBC, and many others.
    upload all of their Tv exhibits on their web sites, and people are viewable without cost.
    It creates a more safe and personal web connection.

  2. Baudolino
    Baudolino

    Hi, I’ve succesfully applied your code. I also made an adjustment to add entries from the next empty cell. Thus I’m able to create a list from different directories. Since I’m a total dummy where it comes to understanding VBA, I thought I’d use an expression in stead of a fixed value. But I seem to refer in a wrong way, because it doesn’t work 🙁
    Could you help me with this? Most grateful if you would!
    I made an adjustment in line 69 and created this prose:
    entryA = “A” & (Range(“A1”).End(xlDown).Row + 1)
    entryB = “B” & (Range(“B1”).End(xlDown).Row + 1)
    Sheets(“Inventarisatie”).[entryA].Resize(AllFiles.Count, 1) = WorksheetFunction.Transpose(AllFiles.keys)
    Sheets(“Inventarisatie”).[entryB].Resize(AllFiles.Count, 1) = WorksheetFunction.Transpose(AllFiles.Items)

    • Baudolino
      Baudolino

      @Baudolino I managed to solve the problem! I created the following lines:

      Sheets(“Inventarisatie”).Range(entryA).Resize(AllFiles.Count, 1) = WorksheetFunction.Transpose(AllFiles.keys)
      Sheets(“Inventarisatie”).Range(entryB).Resize(AllFiles.Count, 1) = WorksheetFunction.Transpose(AllFiles.Items)

      Thanks again for this code. My modification has made it possible to add listings from different directories into one listing

  3. JCstdio
    JCstdio

    Hi,
    thanks for the great code, I’ve made a change, so I can get only specific files, but it enters in an infinite loop in the FeatureFiles, here the code, any ideas?

    For Each Key In AllFolders.Keys
    NodeFiles = Dir(Key & “*Node*.html”)
    FeatureFiles = Dir(Key & “*Feature*.html”)

    Do While NodeFiles “”
    AllFiles.Add (Key & NodeFiles), “”
    NodeFiles = Dir
    Loop
    Do While FeatureFiles “”
    AllFiles.Add (Key & FeatureFiles), “”
    FeatureFiles = Dir
    Loop
    Next

  4. JCstdio
    JCstdio

    Ok, stupid thing,

    I have to put it this way, getting the FeatureFiles after the the NodeFiles Loop
    For Each Key In AllFolders.Keys
    NodeFiles = Dir(Key & “*Node*.html”)

    Do While NodeFiles “”
    AllFiles.Add (Key & NodeFiles), “”
    NodeFiles = Dir
    Loop

    FeatureFiles = Dir(Key & “*Feature*.html”)
    Do While FeatureFiles “”
    AllFiles.Add (Key & FeatureFiles), “”
    FeatureFiles = Dir
    Loop
    Next

    Now I have another Issue,

    I’m trying to return the AllFiles object as Object.

    I’m using it like that
    Function AllFiles() As Object
    ‘ All the code here

    End Function

    And calling it in a Sub like that

    Sub load_data()
    Files = AllFiles
    End Sub

    But I’m getting a “Wrong number of arguments or invalid property assignment

  5. alan
    alan

    hey thanks for the code, it works great. can you please tell me, how can i make the filename to be a hyper link to the actual document?

  6. Johan
    Johan

    You can use this:
    ActiveSheet.Hyperlinks.Add Anchor:=Range(“a1”), Address:=FilePath, TextToDisplay:=FileName

  7. Johan
    Johan

    Very nice code! Thank you very much.

    Ran in to some problems when the filname contained kyrillian letters. But they were quite few.

  8. Norm
    Norm

    This code seems to work very well for most folders, but has problems when I run it on C:\Users\Public. According to File Explorer, that folder has 133,987 files. The code lists 63746 files , then 65536 #N/A. What is the problem and how can it be corrected?

  9. Ginsberg
    Ginsberg

    Just guessing but you could check in explorer folder options to see if you have “dont show hidden files and folders” selected.

    in explorer click “file”, then “change folder and search options” , new dialog box select “View tab” – check hidden files and folders options

  10. Ginsberg
    Ginsberg

    Just guessing but you could check in explorer folder options to see if you have “dont show hidden files and folders” selected.

    in explorer click “file”, then “change folder and search options” , new dialog box select “View tab” – check hidden files and folders options

    Hey Norm, thinking about it ,the code didnt run for me at first. i got a “variable not defined” referring to “Key”
    so (a little blindly if im honest) i added – “Dim Key As Variant” to the declarations and it ran perfectly. when it ran and crashed the first time it did list e few sytem files,.

    good luck