List Of All Files In A Folder

**Excel Fox** · 10-24-2011, 10:26 PM

This macro creates a list of all files within a folder.

Excel 2007+ version

Code:

Sub ListAllFile()

    Dim objFSO As Object
    Dim objFolder As Object
    Dim objFile As Object
    Dim wks As Worksheet
    
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set wks = Worksheets.Add
    
    'Get the folder object associated with the directory
    Set objFolder = objFSO.GetFolder("C:\")
    wks.Cells(1, 1).Value = "The files found in " & objFolder.Name & "are:"
    
    'Loop through the Files collection
    For Each objFile In objFolder.Files
        wks.Cells(wks.UsedRange.Rows.Count + 1, 1).Value = objFile.Name
    Next
    
    'Clean up!
    Set objFolder = Nothing
    Set objFile = Nothing
    Set objFSO = Nothing

End Sub

Excel 2003- version

Code:

Sub ListAllFiles()

    'Not for Excel 2007
    Dim objFileSearch As FileSearch, wks As Worksheet, lngLoop As Long
    Set objFileSearch = Application.FileSearch
    With objFileSearch
        .SearchSubFolders = False ' set to true if you want sub-folders included
        .FileType = msoFileTypeAllFiles 'can modify to just Excel files eg with msoFileTypeExcelWorkbooks
        .LookIn = "C:\"  'modify this to where you want to serach
        If .Execute > 0 Then
            Set wks = Worksheets.Add
            For lngLoop = 1 To .FoundFiles.Count
                wks.Cells(lngLoop, 1) = Mid$(.FoundFiles(lngLoop), InStrRev(.FoundFiles(lngLoop), "\") + 1)
            Next
        Else
            MsgBox "No files found"
        End If
    End With
    
    Set objFileSearch = Nothing
    Set wks = Nothing
    lngLoop = Empty
    
End Sub