total number of files for a given drive

Harry Covair · Aug 15, 2012

Hi the forum,

I want to know the total number of files (including files in subfolders) for a given drive.

The properties dialog window for a folder gives the information.

Is there an Excel (64 bits) VBA solution to retrieve this information?

Thanks in advance

Regards

Harry

SirJB7 · Aug 15, 2012

Hi, Harry Covair!

Give a look at this file:

https://dl.dropbox.com/u/60558749/total%20number%20of%20files%20for%20a%20given%20drive%20%28for%20Harry%20Covair%20at%20chandoo.org%29.xlsm

Go to VBA editor, sheet Hoja 1, and check the macro DirCount.

This is the code:

-----

[pre]

Code:

Option Explicit

Sub DirCount()
&#39; constants
Const ksComSpec = &#34;comspec&#34;
Const ksShellClose = &#34; /c &#34;
Const ksShellCommand = &#34;dir /s &#34;
Const ksRedirect = &#34; &#62;&#34;
Const ksDirFile = &#34;dir.txt&#34;
&#39; declarations
Dim sDir As String, lFolders As Long, lFiles As Long
Dim I As Integer, J As Long, A As String
&#39; inicio
sDir = InputBox(&#34;Enter folder (full path):&#34;, &#34;Parameter&#34;, ThisWorkbook.Path)
If sDir = &#34;&#34; Then Exit Sub
lFolders = 0
lFiles = 0
&#39; process
&#39;  dir
A = Environ$(ksComSpec) & ksShellClose & ksShellCommand & sDir & ksRedirect & ksDirFile
Shell A, vbHide
&#39;  dir file
I = FreeFile
A = ThisWorkbook.Path & Application.PathSeparator & ksDirFile
Open A For Input As #I
On Error Resume Next
Do While Not EOF(I)
&#39; read
Line Input #I, A
A = Trim$(A)
J = Left$(A, InStr(A, &#34; &#34;) - 1)
&#39; keep last two
lFiles = lFolders
lFolders = J
Loop
On Error GoTo 0
Close #I
&#39; end
MsgBox &#34;Folder &#34; & sDir & &#34; contains &#34; & lFolders & &#34; folders and &#34; & lFiles & &#34; files&#34;, _
vbInformation + vbOKOnly, &#34;Summary&#34;
End Sub

[/pre]
-----

It works for any Excel version in number or in bits.

Regards!

Harry Covair · Aug 15, 2012

Hi SirJB7,

Thanks for your answer. It is the solution I was looking for. In fact, it should be.

By running your macro, it stops on the instruction "Open A For Input As #I" and the message "file not found" appears on the screen.

As I undertand the code, the goal of the instruction "A = Environ$(ksComSpec) & ksShellClose & ksShellCommand & sDir & ksRedirect & ksDirFile" is to create a text file A.txt in the specified directory. There is no trace of such a file.

As I do not master this kind of intruction, could you help me to solve the problem.

thanks in advance

Regards

Harry

Harry Covair · Aug 15, 2012

Correction

is to create a text file dir.txt

Harry Covair · Aug 15, 2012

I found the file in folder C:UsersHCDocuments

I modified the code replacing A = ThisWorkbook.Path & Application.PathSeparator & ksDirFile by A = "C:UsersJMDocuments" & Application.PathSeparator & ksDirFile

The code runs to the end and gives good results for any subfolder of C:. But when I ask to run on C:, the results are absolutely not correct (3 files found)

Harry

SirJB7 · Aug 15, 2012

Hi, Harry Covair!

The first macro version used the actual folder of the container workbook file to create the "dir.txt" text file issuing a Shell "dir" command with "/s" parameter for subfolders recursion, via the "cmd.exe" command line processor as set in environment variable "comspec" (required as Shell doesn't handle internal commands). But it had asynchronous issues regarding the time the Dir command lasted to create the temp file.

So here's the new version. It creates a "dir.cmd" temp file that can be shelled normally (without comspec help).

Please download the updated file from same link.

Regards!

EDIT: Posted without having read your last comment. Will check it tomorrow.

Debraj · Aug 15, 2012

Hi Harry,

Until SirJB7 give us the updated code (tomorrow)..

can you please test with the attache file.

https://dl.dropbox.com/u/78831150/Excel/total%20number%20of%20files%20for%20a%20given%20drive%28Harry%20Covair%29.xls

Refer : http://excelexperts.com/VBA-Tips-List-Files-In-A-Folder

for more detail.

@ SirJB7:

You are awesome in Kernel based Power Shell Programming..

Regards,

Deb..

SirJB7 · Aug 15, 2012

@Debraj Roy

Hi!

Then I did reinvent the wheel with this:

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

2012 vs. 2009

Regards!

PS: while DOS's still alive, why don't profit of it?

bobhc · Aug 15, 2012

Debraj Roy

Quote "Kernel based Power Shell Programming" I do that........but we call it shelling nuts

Harry Covair · Aug 16, 2012

Hi SirJB7,

Thanks for correcting your first solution. The second version is working fine.

In fact, the ultimate goal is to know the total number of files in a directory to set the upper limit of a progress bar used in another process (find all "*.xls" files containing a macro without opening them and save them as *.xlsm". I still have work cut out.

@Debraj Roy

elegant solution to list files from a directory and very good explanation about scripting objects in the site

Regards

Harry

PS. I finish the current developments and I come back with a new thread to try to solve a problem that bothers me for months.

SirJB7 · Aug 17, 2012

Hi, Harry Covair!

Made a little update for whole drives, don't need to enter the backslash, just the unit letter and the colon, because of an issue regarding current drive letter (it lacked the backslash so it displayed contents of current folder not whole drive).

Please download again the file from same previous link.

Regards!

shrivallabha · Aug 17, 2012

Here's few more versions:

FSO and Shell based:

http://www.vbaexpress.com/forum/showthread.php?t=42482

Dir method and DOS based:

http://www.mrexcel.com/forum/excel-questions/646897-getting-file-listing-dir.html

Harry Covair · Aug 17, 2012

Hi SirJB7,

Thanks for correcting. But I don't see any difference. In both versions, c: and c: give a different result. See the test results in

https://www.dropbox.com/s/z1u5otvqozi79hd/test%20version%202%20and%203%20SirJB7%20Chandoo.docx

Really, in my case the difference is not significant as it is only the max value for a progress bar.

@ shrivallabha

I will take some time next week to have a look on the suggested links.

Have a nice weekend

Regards

Harry

SirJB7 · Aug 17, 2012

Hi, Harry Covair!

Just tested on my C drive and worked fine, give a look at this file:

https://dl.dropbox.com/u/60558749/total%20number%20of%20files%20for%20a%20given%20drive%20%28for%20Harry%20Covair%20at%20chandoo.org%29.png

The difference of 1 is a file recently created.

Regards!

EDIT: I now realize you're using the older version, newer says "Drive" instead of "Folder" when path length is less than 5. Download it again, please.

SirJB7 · Aug 17, 2012

@shrivallabha

Hi!

Even I couldn't see first version of vbaexpress.com as I'm not registered there, I have used all three methods (VBA Dir, FSO and DOS Dir) for different purposes.

This time I chose DOS dir with redirection because of speed, more if Harry Covair wanted it for a whole drive.

Regards!

shrivallabha · Aug 17, 2012

Hi SirJB7,

You should try Shell object method as well as it is faster than FSO IMHO. I have uploaded one file which I had created for VBAExpress.

https://www.box.com/s/4e98da2f5878ccf8fa15

SirJB7 · Aug 17, 2012

@shrivallabha

Hi!

Trying to being fair I decided to crop your code so as to just count and display the number of folders and files. I guess I did something wrong as I get the error you could see in these images:

https://dl.dropbox.com/u/60558749/total%20number%20of%20files%20for%20a%20given%20drive%20%28for%20Harry%20Covair%20at%20chandoo.org%29%201.png

https://dl.dropbox.com/u/60558749/total%20number%20of%20files%20for%20a%20given%20drive%20%28for%20Harry%20Covair%20at%20chandoo.org%29%202.png

The cropped code is this:

-----

[pre]

Code:

Option Explicit
Option Compare Text &#39;We might come across mixtures of uppercase lowercase letters sometimes
Public objShApp As Shell
Public i As Long

&#39;start SirJB7
Dim lFolders As Long, lFiles As Long
Public Sub XRunFileFolderList()
Dim strPath As String
Debug.Print Now(),
XListItemsInFolder &#34;C:&#34;, True
Debug.Print Now()
MsgBox lFolders & &#34; - &#34; & lFiles
End Sub
Public Sub XListItemsInFolder(strPath As String, boolSubFolder As Boolean)
Dim fldItem As ShellFolderItem
If objShApp Is Nothing Then Set objShApp = New Shell
With objShApp.Namespace(strPath)
For Each fldItem In .Items
If fldItem.IsFolder Then
lFolders = lFolders + 1
Else
lFiles = lFiles + 1
End If
If fldItem.IsFolder And boolSubFolder Then XListItemsInFolder fldItem.Path, boolSubFolder
Next fldItem
End With
End Sub
&#39; end SirJB7

[/pre]
-----

Would you please check it and tell me what to correct? Thank you.

Regards!

shrivallabha · Aug 19, 2012

Hi SirJB7,

The condition for zip file was missing. The code trips if a folder exists inside zip file. Following is amended code which shall work:

[pre]

Code:

Option Explicit
Option Compare Text &#39;We might come across mixtures of uppercase lowercase letters sometimes
Public objShApp As Shell
Public i As Long
&#39;start SirJB7
Dim lFolders As Long, lFiles As Long
Public Sub XRunFileFolderList()
Dim strPath As String
Debug.Print Now(),
lFolders = 0: lFiles = 0
XListItemsInFolder &#34;E:&#34;, True
Debug.Print Now()
MsgBox lFolders & &#34; - &#34; & lFiles
End Sub
Public Sub XListItemsInFolder(strPath As String, boolSubFolder As Boolean)
Dim fldItem As ShellFolderItem
If objShApp Is Nothing Then Set objShApp = New Shell
With objShApp.Namespace(strPath)
For Each fldItem In .Items
&#39;Check for zip file was missing added here
If fldItem.IsFolder And InStr(fldItem.Name, &#34;.zip&#34;) = 0 Then
lFolders = lFolders + 1
Else
lFiles = lFiles + 1
End If
&#39;And here
If fldItem.IsFolder And boolSubFolder And InStr(fldItem.Name, &#34;.zip&#34;) = 0 Then _
XListItemsInFolder fldItem.Path, boolSubFolder
Next fldItem
End With
End Sub
&#39; end SirJB7

[/pre]

SirJB7 · Aug 20, 2012

Hi, shrivallabha!

Thank you for updating the code.

Give a look to this file:

https://dl.dropbox.com/u/60558749/total%20number%20of%20files%20for%20a%20given%20drive%20%28for%20Harry%20Covair%20at%20chandoo.org%29%20-%20comparison.png

It's a composed image with 8 runs of code, 2 sets of drive & folder from same drive, both with your code and mine (cropped as well as to be fair too), run each twice:

First selection, 725 folders & 5895 files, M:Fotos

1st 2 runs, your code, within the second

2nd 2 runs, my code, within the second too

So expand the range...

Second selection, 3168 folders & 22012 files, M:

3rd 2 runs, my code, within the second

4th 2 runs, your code, one second of difference

My humble conclusion: both methods are quite fast and there's no significant advantage. Indeed there might be an error of 1/2" (half of a second) introduced by the exact moment in which each process is run (which I tried to eliminate running each code twice for each data set), but I should go to API calls for counting ticks... and I think it wasn't worth the effort. Both methods are amazingly fast, but I still keep betting on "Shell .cmd with dir /s" and extracting the last 2 values.

Regards!

PS: Drive M is a WD 750Gb 2.5" SATA II notebook drive connected to an interface USB/SATA2, being used for backup purposes... until I decide to open my notebook and change the original 160Gb for this one. Just fyi, as I think that running from SATA II or III directly it would have stretched the times even more.

shrivallabha · Aug 20, 2012

SirJB7,

Thanks for feedback and efforts.

The significant speed advantage is not when you are "counting" files but when you are "listing" files in the sense that we can control shell object during loop while we have to process DOS output.

Secondly, DOS output can't be extended to give attributes while Shell can be extended to get attributes as well.

SirJB7 · Aug 20, 2012

Hi, shrivallabha!

First point, I don't use DOS output for that, check my Documenting & Inspector topic. But yes for this case when only counting was required.

Second point, I agree and never said so, check too my next Folders & Files topic. Coming soon...

Regards!

Harry Covair · Aug 29, 2012

Hi SirJM7,

The code calculating the number of files in a folder or disk runs very well, with one exception: if the folder to be analyzed is in "read only" mode, the file "dir.txt" can therefore not be written and the code loops on the statements "for loop asynchronic issues".

But what I do not understand is that the file "DIR.CMD" is successfully created.

Do you have any idea to overcome this problem?

Thank you in advance

Harry

SirJB7 · Aug 29, 2012

Hi, Harry Covair!

Both files, script command Dir.cmd and text Dir.txt, are created in the same folder of the workbook and its path it's fully qualified as follows:

sDirFile = ThisWorkbook.Path & Application.PathSeparator & ksDirFile

sCmdFile = ThisWorkbook.Path & Application.PathSeparator & ksCmdFile

So no matters whether the folder to be analized is in read only mode or on a read only drive, network drive or device (DVD): what rules is the workbook's folder. And of course the solution it's not intended to be run from a non writable unit or protected folder, and if one is created so does the other.

If Ok with this, let me ask if you tried stopping the code before creating first file?

Set a breakpoint at first line "I = FreeFile" and press F8, check first for proper deletion of both files, then for the creation of the script command file, then for the issuing of Dir command, then for the creation of the text file.

If you haven't done so, please go thru those steps and tell me what happens. If you did it yet, just advise and tomorrow I'd try to check it exhaustively again.

Regards!

Harry Covair · Aug 29, 2012

Hi Sir JB7,

Your detailed answer could not be more correct: the read-only mode has nothing to do, it was a hasty conclusion. Really sorry.

But I think I have pinpointed other problematic circumstances.

If the name of the folder to analyze (where dir.cmd and dir.txt are written) contains a space, the instruction Shell sCmdFile, vbHide cannot be executed (if I remember correctly, DOS does not accept spaces) and a message RTE #5, Invalid procedure call appears on the screen. When I remove the space, the code runs without problem. The solution I found is a “for” loop with a “mid” statement to test if there is a space in the name.

If the selected folder requires a specific authorization (e.g. the default user folder), a RTE #75 Path/File Access error is generated. I tried Err.Number = 75 but in vain.

Another problem is the, in some cases, big difference between the results given by the Dir statement in comparison with the number of listed files. For example, my C: disk contains 243325 files according to the “lfiles” variable while I count 247895 listed files. Does the Dir statement ignore some attributes?

Once again I will thank you for your attention to my problem and, more importantly, congratulate you for the relevance of your answers.

Harry

SirJB7 · Aug 29, 2012

Hi, Harry Covair!

Glad to hear that you solved your first issue, so I have to work less

Folder with spaces in name: you're right, I'm thinking how to fix it, a temporary approach is to enter the short form (8.3) of folder path, nice, isn't it?

Difference in folders & files: you'r right, and it's due to how Dir command works on attributes. And how Windows Explorer does it too! Check my C drive figures:

Normal Dir -----> 187469/343363

Dir /a:d /a:h /a:s /a:l /a:r /a:a /a:i /a: /s -----> 189671/358107

Windows Explorer, displaying all files, drive with all folders selected, properties: ----> 60830/339291

Which one is actually true?

I don't know and it's far beyond from this topic scope, I guess. For normal drives it works, for system drives I think it works more user-side accurate with normal Dir.

So maybe you want to tell us what are you intending to use this workbook for and I'd tell you my opinion about its usage.

Regards!

PS: BTW, thank you for your kind words.

total number of files for a given drive

Member

Excel Rōnin

Member

Member

Member

Excel Rōnin

Excel Ninja

Excel Rōnin

Excel Ninja

Member

Excel Rōnin

Excel Ninja

Member

Excel Rōnin

Excel Rōnin

Excel Ninja

Excel Rōnin

Excel Ninja

Excel Rōnin

Excel Ninja

Excel Rōnin

Member

Excel Rōnin

Member

Excel Rōnin