Number of files in a folder

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

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

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

---
Regards,
Norman


<[email protected]> wrote in message
news:[email protected]...
> 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
>

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
--
Tim Williams
Palo Alto, CA


<[email protected]> wrote in message
news:[email protected]...
> 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
>

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

Hi P,

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

---
Regards,
Norman


<[email protected]> wrote in message
news:[email protected]...
> 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
>

Hi Dave,

> flakey ?

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


---
Regards,
Norman



"Desert Piranha"
<[email protected]> wrote in
message news:[email protected]...
>
> Hi Norman,
> flakey ?
> Dave
> Norman Jones Wrote:
>> 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
>>
>> ---
>> Regards,
>> Norman
>
>
> --
> Desert Piranha
>
>
> ------------------------------------------------------------------------
> Desert Piranha's Profile:
> http://www.excelforum.com/member.php...o&userid=28934
> View this thread: http://www.excelforum.com/showthread...hreadid=501773
>

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.

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

<[email protected]> wrote in message
news:[email protected]...
> 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.
>

> 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

> 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)

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

NickHK
<Sticking with W2K>


<[email protected]> wrote in message
news:[email protected]...
> > 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)
>

> 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

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.

---
Regards,
Norman


<[email protected]> wrote in message
news:[email protected]...
>> 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
>

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

NickHK

<[email protected]> wrote in message
news:[email protected]...
> > 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
>