Find filename, path, document size, filename length and path length?

Hello Laneyboggs,

This macro lets you choose your starting folder and whether you want to list all the sub-folders. You can also choose the starting cell on the worksheet. If you don't supply a starting cell, the default is "A2" of the active worksheet. This macro works with Excel 2000 and later.

'Written: April 30, 2010
'Author:  Leith Ross

Sub ListAllFiles(ByVal SourceFolderName As String, Optional IncludeSubfolders As Boolean, Optional WksCell As Range)

  Dim FSO As Object
  Dim SourceFolder As Object
  Dim SubFolder As Object
  Dim FileItem As Object
  
     Set FSO = CreateObject("Scripting.FileSystemObject")
     Set SourceFolder = FSO.GetFolder(SourceFolderName)
     
     If WksCell Is Nothing Then
       'Default starting cell is A2 on the ActiveSheet
        Set WksCell = Range("A2")
     End If
     
      'List files in the Folder one column to the right
       For Each FileItem In SourceFolder.Files
         WksCell.Offset(0, 0) = FileItem.Name
         WksCell.Offset(0, 1) = FileItem.Path
         WksCell.Offset(0, 2) = FileItem.Size
         WksCell.Offset(0, 3) = Len(FileItem.Name)
         WksCell.Offset(0, 4) = Len(FileItem.Path)
        'List the Files in the Folder starting 1 row down
         Set WksCell = WksCell.Offset(1, 0)
       Next FileItem
       
      'List Subfolders if option is True
       If IncludeSubfolders Then
         For Each SubFolder In SourceFolder.SubFolders
           ListAllFiles SubFolder.Path, True, WksCell
         Next SubFolder
       End If
    
   'Release objects and free memory
    Set SourceFolder = Nothing
    Set SubFolder = Nothing
    Set FileItem = Nothing
    Set FSO = Nothing
    
End Sub

Example of Calling the Macro from VBA

  'List only the files in the folders
    ListAllFiles "C:\Documents and Settings\Owner\Start Menu"
  'Or this way
    ListAllFiles "C:\Documents and Settings\Owner\Start Menu", False

  'List all files in the folder and all files in all sub-folders
    ListAllFiles "C:\Documents and Settings\Owner\Start Menu", True