total number of files for a given drive

[Harry Covair]

Hi Sir JB7,

The initial goal is not changed: to know the total number of files that should be the upper limit of a progress bar.

I say "should be" because I left the progress bar because Excel 2010 will go into "no response" state when the treatment time is too long (even with some “do events” statements). I replaced it with information in the status bar. A "Select Case" gives empirically the treatment time. For example, for more than 200.000 (coming from the variable lfiles) files the message "estimated treatment time > 10 minutes” appears in the status bar. So 220000 or 240000 detected files is - in this case - without importance.

The observations described in my previous message had for sole purpose of informing you as feedback.

If you have a look at

https://www.dropbox.com/s/oh4stecfgpkehc2/files%20management%20Harry.xlsm

you will see the current state of the application.

But if you find a solution using the short DOS name, it would be great.

Regards

Harry

 

[SirJB7]

Hi, Harry Covair!


Forgive me if I sounded impolite, that wasn't the idea and I really appreciate your feedback and your observations were taken as constructed criticism. Thanks for uploading your file, I never suspected to find you writing in French but now regarding your profile I think it's usual for a Belgian, isn't it? BTW my native language it's Spanish (Argentine).


Testing your workbook I found these points, in order of appearance not of importance:


- worksheet name: change "Start (do not delete)" to "Commencer (ne supprimez pas)"


- cell B1: optionally make "heure" uppercase


- cell E1: make it a little wider so as "Durée (sec)" fits without being cropped


- user form: translate "Start" to "Commencer" and "Quit Form" to "Quitter Forme"


- Start button: if selecting a drive ("C:") I get a 75 run time error in line (of module Mod_CountFiles):

Open sCmdFile For Output As #I

so you should change these previous lines:

sCmdFile = sdir & Application.PathSeparator & ksCmdFile

sDirFile = sdir & Application.PathSeparator & ksDirFile

by these:

sCmdFile = ThisWorkbook.Path & Application.PathSeparator & ksCmdFile

sDirFile = ThisWorkbook.Path & Application.PathSeparator & ksDirFile

to avoid the double backslash issue (even placing workbook in root folder)


- Status bar: I'm not running a HPC workstation but it's well over the average (so as to seem modeste, in fact it's a Gigabyte GA-EX58-UD5 running an i7-920 with 6Gb 2000Mhz & GPU 1Gb DDR5 & 2x1Tb WD Caviar Black & the list continues...) and it takes a lot of time 30 sec?- to display the status bar when chosen not small folders, I know that it's mostly the time that Dir takes to run but perhaps you could implement a waiting form like in my 2 below links (just an idea)


- All modules and Excel objects code sections: always use "Option Explicit" statement, it's safer, harmless and forces you to be more careful


- ThisWorkbook, Sub Workbook_Open (and all other modules and occurrences): use With...EndWith structures to force Excel caching and speeding process with same objects

With UF1

... all stuff here

End With


- generated worksheets "HH h MM": freeze first line or from B2 or C2 cells as you needed


Despite of all this, your generated worksheets "hh h mm" are almost exactly the same as those generated by the files of these 2 mine topics at chandoo's website:

http://chandoo.org/forums/topic/excel-files-documenting-inspector

http://chandoo.org/forums/topic/folders-files-matching

Just for if you want to give a look at them and extract any ideas.


Some Aha! Inspiration ((C)(R) by Martin Gardner) has just crossed my mind. Let me play a little around and come back to you later, regarding 8.3 notation folder issue.


Regards! (Ou est-ce que je dois dire salut?) (in English, or should I say regards?)


PS: I'll be playing over my last uploaded workbook with many of the modifications suggested here, so you'd then opt to implement them on your file.


EDIT:


As I wrote to shrivallabha upwards:

http://chandoo.org/forums/topic/total-number-of-files-for-a-given-drive#post-37963

I'd use the redirected Dir command output just for code simplicity and processing speed, but only in the case where I only needed the number of folders and files within a folder or drive. If I needed listing the files with all their attributes I'd definitively use the file system object as in the 2 other projects, with an user form or a status bar to indicate elapsed time and progress.


If I were you, I'd seriously consider this alternative.


Regards!