File Inventory

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()         Workbooks.Add ' create a new workbook for the file list     ' 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