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


wrote in message
oups.com...
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


wrote in message
oups.com...
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


wrote in message
ups.com...
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 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

Hi Dave,

flakey ? :)

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


---
Regards,
Norman



"Desert Piranha"
<Desert.Piranha.21rttm_1137488101.4389@excelforu m-nospam.com wrote in
message news:Desert.Piranha.21rttm_1137488101.4389@excelfo rum-nospam.com...

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

wrote in message
oups.com...
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


wrote in message
oups.com...
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


wrote in message
oups.com...
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

wrote in message
oups.com...
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