Number of files in a folder

[pascalv]

Does somebody know why, in some cases, these 2 programs return
different results (for finding the number of files of a folder)?

In both cases, the aim was to count *all* files

Thanks!


----- Idea 1: Use of Application.FileSearch

Public Sub test1()
Dim fs As Object
Set fs = CreateObject("Scripting.FileSystemObject")

With Application.FileSearch
.NewSearch
.RefreshScopes
.FileTypes.Add msoFileTypeAllFiles
.FileType = msoFileTypeAllFiles
.SearchSubFolders = True

.LookIn = "C:\users"

ActiveCell.Formula = .Execute(SortBy:=msoSortByLastModified, _
SortOrder:=msoSortOrderAscending, _
AlwaysAccurate:=True)
End With

Rem Of course Application.FileSearch.FoundFiles.Count
Rem does Not work either...
End Sub

----- End of idea 1


----- Idea 2: Use a recursive function

Public Sub test2()

Dim fs As Object
Set fs = CreateObject("Scripting.FileSystemObject")
ActiveCell.Formula = nbFiles("C:\users", fs)

End Sub


Private Function nbFiless(folderName As String, ByRef fs As Object)

Dim f As Object

nbFiles = fs.GetFolder(folderName).Files.Count

If Not (fs Is Nothing) Then
If fs.GetFolder(folderName).SubFolders.Count > 0 Then
For Each f In fs.GetFolder(folderName).SubFolders
nbFiles = nbFiles _
+ nbFiles(f.Path, fs)
Next
End If
End If
End Function

----- End of Idea 2

 

[Mark Lincoln]

Just a wild guess on my part: Does one of these routines find hidden
files that the other doesn't?

 

[Norman Jones]

Hi P,

There have been munerous posts suggesting that Filesearch , at least in its
xl2002 implementation is very flakey.

See for example:

http://tinyurl.com/6p6vl
and

http://tinyurl.com/6cen2

 

[Tim Williams]

Was one of the answers correct? Is your posted code *exactly* what you used?

As written it would not work correctly:

Private Function nbFiless(folderName As String, ByRef fs As Object)

Dim f As Object

nbFiles = fs.GetFolder(folderName).Files.Count

If Not (fs Is Nothing) Then
If fs.GetFolder(folderName).SubFolders.Count > 0 Then
For Each f In fs.GetFolder(folderName).SubFolders
nbFiles = nbFiles _
+ nbFiles(f.Path, fs)
Next
End If
End If
End Function

Note the name of your function "nbFiless" is different from its calls to
itself in the "recursive" code. Als othe code seems a bit odd: eg. you are
checking to see if fs is not nothing *after* already having used it.

Tim

 

[pascalv]

Thanks for your review

- None of the answers really solve my problem. If
Application.FileSearch is "flakey", is there any built-in alternative?

- Corrected code: You are right, the correct code should have been:

'----- Idea 2
Private Function nbFiles(folderName As String, ByRef fs As Object)

Dim f As Object

If Not (fs Is Nothing) Then
nbFiles = fs.GetFolder(folderName).Files.Count
If fs.GetFolder(folderName).SubFolders.Count > 0 Then
For Each f In fs.GetFolder(folderName).SubFolders
nbFiles = nbFiles _
+ nbFiles(f.Path, fs)
Next
End If
End If
End Function

 

[Norman Jones]

Hi P,

You already have an alternative scripting solution but look at the Dir
function.

 

[Desert Piranha]

Hi Norman,
flakey ?
Dave

Hi P,

There have been munerous posts suggesting that Filesearch , at least in
its
xl2002 implementation is very flakey.

See for example:

http://tinyurl.com/6p6vl
and

http://tinyurl.com/6cen2

 

[Norman Jones]

Hi Dave,

flakey ?

I accept the implicit rebuke: potentially subject to erratic, unpredictable
results might have been preferable.


---
Regards,
Norman



"Desert Piranha"

 

[pascalv]

Thanks for the Dir function tip
But, Application.FileSearch (if working correctly) has a huge advantage
over Dir: it can list *all* files in a folder and subfolders and sort
them by date. You would need to code time-consuming loops to do that
with scripts.

 

[NickHK]

pascalv,
Whilst it's true that you would need to code a little more, if .FileSearch
returns rubbish, it's irrelevant how compact it is.

NickHK

 

[pascalv]

it's irrelevant how compact it is

"time-consuming": *execution* time (to read all files and sort them by
date), Not the time necessary to write the program

 

[pascalv]

Does somebody know why, in some cases, these 2 programs return

fferent results (for finding the number of files of a folder)?

both cases, the aim was to count *all* files

It seems that some files that are Not listed correctly are .zip and
..msg files
(Application.FileSearch with Win XP SP1 and Excel 2002 SP3)

 

[NickHK]

pascalv,
Doesn't XP have the ability (option ?) to treat zipped files as folders ?

NickHK
<Sticking with W2K>

 

[pascalv]

Doesn't XP have the ability (option ?) to treat zipped files as folders ?

In the file explorer: maybe, but with Application.FileSearch zip files
are Not treated as folder Neither as files

Cheers

 

[Norman Jones]

Hi Pascal,

As indicated earlier in this thread, there have been numerous reports of
unreliable results produced by the use of Filesearch with recent versions of
Excel - some relating specifically to zip files.

I am not aware of any reported solution which overcomes these
unreliabilities in the use of Filesearch.

 

[NickHK]

pascalv,
With all the problems then, it would probably be a good idea not to use
..Filesearch and roll you own.

NickHK