List files properties from a folder

Tested in Excel 365 (16.8730.2046) 64-bit. All links open in new tab.

 
 
  1. Public Sub ListFileProperties()
  2. ' need to add the following reference:
  3. ' Microsoft Scripting Runtime
  4.    Dim fso As New FileSystemObject, folder As Variant, file As Variant
  5.     Dim currRow As Long, folderPath As String
  6.        
  7.     Application.DisplayAlerts = False
  8.     If SheetExists("Files List") Then Sheets("Files List").Delete
  9.     Application.DisplayAlerts = True
  10.    
  11.     folderPath = PickFileOrFolder(0)
  12.    
  13.     If folderPath = "" Then Exit Sub
  14.     Set fso = CreateObject("Scripting.FileSystemObject")
  15.    
  16.     If fso.FolderExists(folderPath) Then
  17.         Set folder = fso.GetFolder(folderPath)
  18.     Else
  19.         MsgBox "Folder doesn't exist!", vbCritical, "Warning"
  20.         Exit Sub
  21.     End If
  22.    
  23.     Sheets.Add
  24.     Range("A1").Value = "File Name"
  25.     Range("B1").Value = "Creation Date"
  26.     Range("C1").Value = "Modified Date"
  27.     Range("D1").Value = "File Size"
  28.    
  29.     currRow = 2
  30.     For Each file In folder.Files
  31.         Range("A" & currRow).Value = file.Name
  32.         Range("B" & currRow).Value = file.DateCreated
  33.         Range("C" & currRow).Value = file.DateLastModified
  34.         Range("D" & currRow).Value = file.Size
  35.        
  36.         currRow = currRow + 1
  37.     Next
  38.    
  39.     ActiveSheet.Name = "Files List"
  40.     Range("A" & currRow).Value = folderPath
  41. End Sub

 

PickFileOrFolder function is here

SheetExists function is here

Add a Comment

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.