better way to get recursive file/folder count from VBA via Windows Explorer properties "Contains"?

[madscijr]

In Windows Explorer, when you right-click a folder and select Properties (or just select the folder and press ALT+ENTER), you get all sorts of useful information, including the total count of files and folders contained inside (not just directly inside, but the total recursive count).

I may be doing it wrong, but my understanding of VBA's FileSystemObject Folder object is that to get this total count of folders and/or files you have to recursively iterate through each subfolder and add up the Folder.Files.Count or Folder.Folders.Count to get the total total count. I haven't seen a "total total" file or folder count property for the Folder object.

For folders with many many files, this recursive counting method can take a while. VBA took 8+ minutes to get a count for the contents of a folder on a large hard drive. But when I click on the folder and get properties, Windows Explorer gave me the file+folder count in under 5 seconds!

So my question for you VBA gurus is: how, using either an API call or whatever way that is not too involved (ie it will run in most people's (Windows 7/8) Excel VBA (2007-2013) without installing anything special), can we access the Explorer file/folder "Properties" function, to QUICKLY retrieve a file and folder count of a given folder (and maybe other useful properties too?)

From what I've seen as a Windows user and VBA developer, it seems this sort of thing should be possible. Thoughts?

 

[sn152]

Hi,

Please check the attached file. Not sure whether or not you are looking for this. But this gives you the details of all the files in a folder and sub folder and this is pretty quick.

 

[madscijr]

Thanks for your reply, that method is what I have been doing and which takes forever with a large number of files (have you tried running that on a folder that contains >10,000 files and subfolders?)

Now try going to the same folder in Windows Explorer, right-click on the folder, and click Properties, and see how quickly Windows returns a file/folder count.

That is the functionality I am looking to harness from VBA via API call, WMI, or some other way.

BTW (you might already know this and just left these out to keep the example simple) but in the script you included above, I noticed a few issues...

1. it's generally a good idea to release your objects from memory in code after you are done with them, at the end of Sub ListFiles you should add:

    set objFSO = nothing
    set objTopFolder = nothing

and at the end of Sub RecursiveFolder you should add:

    set objFile = nothing
    set objSubFolder = nothing

2. include DoEvents inside your loops, so your macro doesn't hog resources and make your PC seem locked up while it's working, for example:

    For Each objFile In objFolder.Files
        DoEvents
        ...
  
        For Each objSubFolder In objFolder.SubFolders
            DoEvents
            ...

3. in Excel VBA you can also speed up processing by temporarily turning off screen updating, calculations, etc., for example:

    Sub RecursiveFolder( ... )
        // TURN OFF EVENTS / SCREEN UPDATES / CALCULATIONS TO SPEED THINGS UP
        Dim bEnableEvents As Boolean :: bEnableEvents = Application.EnableEvents:: Application.EnableEvents = false
        Dim bScreenUpdating As Boolean :: bScreenUpdating = Application.ScreenUpdating:: Application.ScreenUpdating = false
        Dim lngCalculation As Excel.XlCalculation :: lngCalculation = Application.Calculation:: Application.Calculation = Excel.XlCalculation.xlCalculationManual
        ...
        // RESTORE APPLICATION STATE
       Application.EnableEvents = bEnableEvents
       Application.ScreenUpdating = bScreenUpdating
       Application.Calculation = lngCalculation
    End Sub ' RecursiveFolder

(Note it can be helpful to put the sub or function name in a comment on the End Sub or End Function line, just as a visual reminder of what you are looking at...)

PS one pitfall of above can be while you are in the middle of developing and still debugging your macros, something breaks before the routine reaches the end and you get left with events, screen updating, etc., disabled. This can leave Excel in an unusable state & if you try to run your macro again it won't work right and you can waste a lot of time trying to figure out what's wrong. To prevent that, include the following utility function "ResetExcel", and if you CTRL+BREAK your macro, just run it from the Immediate Window...

Public Sub ResetExcel()
    Application.StatusBar = False
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.Calculation = Excel.XlCalculation.xlCalculationAutomatic
  
   '' RE-RUN THE INITIALIZATION SEQUENCE
   'Call InitWorkbook()
End Sub ' ResetExcel

obviously, you can tailor this or the above disabling of events/etc. to your liking, but I find that it does help speed things up.

Thanks again for your reply.

Now... any other ideas about how to tap into Windows Explorer's "Properties" functionality from VBA?

Hi,

Please check the attached file. Not sure whether or not you are looking for this. But this gives you the details of all the files in a folder and sub folder and this is pretty quick.

 

[shrivallabha]

Shell object is faster than FileSystemObject. Please test the code in the attached file.

Here's the link to the original thread where I posted this file.
http://www.vbaexpress.com/forum/showthread.php?42482-More-Robust-Directory-List-Generator

Also some more discussions where we had discussed this:
http://chandoo.org/forum/threads/total-number-of-files-for-a-given-drive.6104/
http://forum.chandoo.org/threads/listing-files-names-and-hyperlinks-in-a-spreadsheet.12086/

You can build your version out of it if it suits you.