A better way of listing folders (and subfolders) contents?

[sark666]

I need to list the folder contents of a drive that's very large. I was doing it previously from a dos prompt and just outputing the file names but I need to automate this and I need the created and modified date.
This lead me to using the Scripting.FileSystemObject. This example seems to make the rounds on numerous sites:

Sub ListFiles()
    'Set a reference to Microsoft Scripting Runtime by using
    'Tools > References in the Visual Basic Editor (Alt+F11)
    
    'Declare the variable
    Dim objFSO As FileSystemObject
    
    'Insert the headers for Columns A through F
    Range("A1").Value = "File Name"
    Range("B1").Value = "File Size"
    Range("C1").Value = "File Type"
    Range("D1").Value = "Date Created"
    Range("E1").Value = "Date Last Accessed"
    Range("F1").Value = "Date Last Modified"
    
    'Create an instance of the FileSystemObject
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    
    'Call the RecursiveFolder routine
    Call RecursiveFolder(objFSO, "C:\Users\Domenic\Documents", True)
    
    'Change the width of the columns to achieve the best fit
    Columns.AutoFit
    
End Sub
Sub RecursiveFolder( _
    FSO As FileSystemObject, _
    MyPath As String, _
    IncludeSubFolders As Boolean)
    'Declare the variables
    Dim File As File
    Dim Folder As Folder
    Dim SubFolder As Folder
    Dim NextRow As Long
    
    'Find the next available row
    NextRow = Cells(Rows.Count, "A").End(xlUp).Row + 1
    
    'Get the folder
    Set Folder = FSO.GetFolder(MyPath)
    
    'Loop through each file in the folder
    For Each File In Folder.Files
        Cells(NextRow, "A").Value = File.Name
        Cells(NextRow, "B").Value = File.Size
        Cells(NextRow, "C").Value = File.Type
        Cells(NextRow, "D").Value = File.DateCreated
        Cells(NextRow, "E").Value = File.DateLastAccessed
        Cells(NextRow, "F").Value = File.DateLastModified
        NextRow = NextRow + 1
    Next File
    
    'Loop through files in the subfolders
    If IncludeSubFolders Then
        For Each SubFolder In Folder.SubFolders
            Call RecursiveFolder(FSO, SubFolder.Path, True)
        Next SubFolder
    End If
    
End Sub

It works, however on a large drive (100,000 + files) it really bogs down and excel shows 'not responding' numerous times and sometimes it doesn't seem like it'll go to completion. Sometimes it's done in about 40 minutes, other times I've left it over an hour and it's still running (while showing not responding).
I'm sure if it's because of numerous calls to vbscript? I read doevents could help this but I tried that within the subfolder check and it didn't seem to help.
Is this unavoidable simply due to the size and amount of sub-folders? If doevents can help here, I'm not really sure on how to use it and it's appropriate place in the code.

Using my dos method of simply outputting the contents to a file works much more efficently but I need to do two passes to capture created and then modified and then I would need to match up the files created and modified and the drive contents could potentially change between two passes.

And just to mention, I did a test on sub directory of this drive (that had about 12 folders with each containing roughly 10 folders in each) and it didn't take too long to complete (about 10 minutes) but again showed not responding numerous times. Doesn't that mean there's something hanging the vba code? I assume it's the vbscript calls.

 

[p9j123]

This is great.. is it possible to only show specific file type, let say *.xls?

 

[p9j123]

Still hoping someone can help me with this.

 

[shg]

You'd have a better chance if you started your own thread instead of responding to one that's four years old.

 

[danielphayward]

Was this in reference to my saving the post? I just wanted to have an easy way to find the thread again. I don't have a specific question I need answered at the moment.

 

[shg]

It was in reference to

Still hoping someone can help me with this.

 

[danielphayward]

Man I feel like such a noob on these forums.