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

Number of files in a folder
 

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

Number of files in a folder
 

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

Number of files in a folder
 

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

Number of files in a folder
 

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

Number of files in a folder
 

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

Number of files in a folder
 

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

Number of files in a folder
 

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

Number of files in a folder
 

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.

Number of files in a folder
 

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.

Number of files in a folder
 

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

Number of files in a folder
 

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)

Number of files in a folder
 

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)

Number of files in a folder
 

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

Number of files in a folder
 

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

Number of files in a folder
 

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