There have been times in my life as an analyst when I have needed a list of all the files in a folder.  I don’t mean copy/paste.  I mean a list of the file names that windows explorer can’t offer.  The list is often used in content inventories and I’ve even had occasion to use it for data entry.

Using snippets of code I ripped from the internet and came up with this Excel macro in VBA. It prompts you for a folder path and you can specify whether you’d like to include files in subfolders or just those at the top level.  You can even use a UNC path or a web folder as the path to create inventories of the files in document libraries (although a datasheet view would provide the same value).

Note: The macro does not have error handling so if you cancel out of the dialogs, you will get an error window and will need to exit the debugging.

Enable developer tools

1. Open Excel, right-click in the Ribbon, and select Customize the Ribbon…

   

2. Check the Developer checkbox in the right pane for Main Tabs

   

3. Click OK

Enable macros

1. Go to the Developer tab
2. Click on Macro Security

   

3. Ensure in Macros Settings you have Disable all macros with notification or Enable all macros... selected

   

4. Click OK

Create the macro

1. On the Developer tab
2. Click the Visual Basic button
3. Paste the code below into the Visual Studio window

   

4. Save the Workbook as an Macro-enabled workbook

   

5. HINT: I save the macro in its own file so I can open it on-demand

Run the macro

1. From the Developer tab, click on the Macros button
2. Double-click on the GetListFilesInFolder macro
3. If no macros are listed in the box, make sure you’ve selected the right workbook in the Macros in drop-down box

   

Sub GetListFilesInFolder() 
    ' create a new workbook for the file list 
    Workbooks.Add 
    
    ' add headers 
    Dim FilePath As String 
    Dim ListSubfolders As Integer
    Dim IncludeSubfolders As Boolean
    Dim HandleBlankPath As Integer
    
    'Create the headers for the list
    With Range("A1")
        .Formula = "Folder contents:"
        .Font.Bold = True
        .Font.Size = 12
    End With
    
    Range("A3").Formula = "File Name"
    Range("B3").Formula = "File Size"
    Range("C3").Formula = "File Type"
    Range("D3").Formula = "Date Created"
    Range("E3").Formula = "Date Last Accessed"
    Range("F3").Formula = "Date Last Modified"
    Range("G3").Formula = "Attributes"
    Range("H3").Formula = "FilePath"
    Range("A3:H3").Font.Bold = True
    
    FilePath = InputBox("Enter the path to list files.")  'Prompt user for the folder to inventory
    
    ListSubfolders = MsgBox("Do you want to include files in subfolders?", vbYesNo, "Subfolders")  'Prompt user to select to include subfolder files or not
    Select Case ListSubfolders
        Case 6 'Yes button
            IncludeSubfolders = True
        Case 7 'No button
            IncludeSubfolders = False
        Case Else 'Assumes Yes as default
            IncludeSubfolders = True
        End Select
        
        ListFilesInFolder FilePath, IncludeSubfolders ' list all files included subfolders
End Sub

Sub ListFilesInFolder(SourceFolderName As String, IncludeSubfolders As Boolean)
    ' lists information about the files in SourceFolder
    ' example: ListFilesInFolder "C:\FolderName\", True 
    
    Dim FSO As Scripting.FileSystemObject
    Dim SourceFolder As Scripting.Folder, SubFolder As Scripting.Folder
    Dim FileItem As Scripting.File
    Dim r As Long
    
    Set FSO = New Scripting.FileSystemObject
    Set SourceFolder = FSO.GetFolder(SourceFolderName)
    r = Range("A65536").End(xlUp).Row + 1
    
    For Each FileItem In SourceFolder.Files
        ' display file properties
        Cells(r, 1).Formula = FileItem.Name
        Cells(r, 2).Formula = FileItem.Size
        Cells(r, 3).Formula = FileItem.Type
        Cells(r, 4).Formula = FileItem.DateCreated
        Cells(r, 5).Formula = FileItem.DateLastAccessed
        Cells(r, 6).Formula = FileItem.DateLastModified
        Cells(r, 7).Formula = FileItem.Attributes
        Cells(r, 8).Formula = FileItem.Path
        
        r = r + 1 ' next row number
    Next FileItem
    
    If IncludeSubfolders Then
        For Each SubFolder In SourceFolder.SubFolders
            ListFilesInFolder SubFolder.Path, True
        Next SubFolder
    End If
    
    Columns("A:H").AutoFit
    
    Set FileItem = Nothing
    Set SourceFolder = Nothing
    Set FSO = Nothing
    ActiveWorkbook.Saved = True
End Sub