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
- Open Excel, right-click in the Ribbon, and select Customize the Ribbon…
- Check the Developer checkbox in the right pane for Main Tabs
- Click OK
- Go to the Developer tab
- Click on Macro Security
- Ensure in Macros Settings you have Disable all macros with notification or Enable all macros... selected
- Click OK
Create the macro
- On the Developer tab
- Click the Visual Basic button
- Paste the code below into the Visual Studio window
- Save the Workbook as an Macro-enabled workbook
- HINT: I save the macro in its own file so I can open it on-demand
Run the macro
- From the Developer tab, click on the Macros button
- Double-click on the GetListFilesInFolder macro
- 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