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.

 

[shg]

Getting all user details within nested email distribution lists of unknown depths from the AD?

Actually navigating hierarchical data structures of unknown depth in general

The example code here traverses a directory structure of unknown depth. IMO, recursive routines sometimes have the advantage of apparent simplicity, but are not always easy to understand.

The only recursive routine I've ever written that I liked (I've written some I didn't) is a useless function that converts column numbers to column letters for the to-be-anounced Excel 2030, which will have two billion columns:

Function ColLtr(ByVal iCol As Long) As String</SPAN>
  ' shg 2012</SPAN>
  If iCol > 0 Then ColLtr = ColLtr((iCol - 1) \ 26) & Chr(65 + (iCol - 1) Mod 26)</SPAN>
End Function</SPAN>

E.g., =ColLtr(210553)

Word is, however, that Microsoft will switch entirely to R1C1 notation before release, to avoid the naughty words and phrases in column names with 4+ letters, so beyond being useless, it will be OBE.

 

[Kyle123]

hmmm, it does indeed that'll teach me to read code more thoroughly I like your clever use of a collection as a queue. It is however situation dependant, you can't however do that sql when querying a self referencing table, not in MSSQL at any rate, you'd need to use a recursive CTE.

The last recursive function I wrote was in .Net for the AD situation I mentioned above. Haha I think some of the naughty words that that would produce would lighten up some of the meetings I find myself in

 

[sark666]

Ok you guys are starting to lose me in the last couple of comments. lol. Still lots to learn.

I guess using recursion has it's uses and can be an elegent way of approaching something. Just from my naive perspective when I first saw it I thought, oh I don't like the looks of that. Just felt like it's asking for weird scenarios.

Regarding the not responding, I did some tests with the last code snippet you posted. The not responding does not happen at all in excel 2003, even with attempting to click anywhere on the interface, but as soon as click anywhere in office 2010, i get the not responding. AS you mentioned it's not an issue, but it seems more rare that it comes up in 2003 so when I see it in 2003 I suspect a serious performance issue.

But ignoring that with the timer you had integrated I ran the same directory on 2010 and 2003:

2003: 111.5 seconds
2010: 94.3 seconds

this was 14,175 files. So pretty good performance. I'm going to adopt your code into mine as I believe it's running faster, but I'll do more tests.

Thanks again for your help.

 

[sark666]

Also I noticed you are using the filedatetime function. I went with vbscript as I want the creation date and modified date. But it seems with filedatetime you get whatever is more recent, which is too bad as it is much faster.

 

[shg]

You're welcome. Post back if you want me to put the workbook on box.

 

[Biz]

You're welcome. Post back if you want me to put the workbook on box.

Hi Shg,

Ur code is very fast and like it. Can you add a browser to select folder or sub folder?

Biz

 

[shg]

See https://www.box.com/s/6fc69d08fdf157f7bd06, Biz.

 

[Biz]

See https://www.box.com/s/6fc69d08fdf157f7bd06, Biz.

Thank you very nice done!

 

[Xcelerator85]

Thanks a lot
it was so useful
but how I could add an option than show me the last user than save or modify the file (excel and word files generally)

 

[danielphayward]

I'm replying so that I can save the thread to look at later.