Number of files in a folder

[pas...@sdf.lonestar.org]

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

---
Regards,
Norman

<pas...@sdf.lonestar.org> wrote in message
news:1137439338.3...@z14g2000cwz.googlegroups.com...

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

<pas...@sdf.lonestar.org> wrote in message
news:1137439338.3...@z14g2000cwz.googlegroups.com...

[pas...@sdf.lonestar.org]

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

[Norman Jones]

Hi P,

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

---
Regards,
Norman

<pas...@sdf.lonestar.org> wrote in message
news:1137485244....@o13g2000cwo.googlegroups.com...

[Desert Piranha]

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?action=getinfo&userid=28934
View this thread: http://www.excelforum.com/showthread.php?threadid=501773

[Norman Jones]

Hi Dave,

> flakey ? :)

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

---
Regards,
Norman

"Desert Piranha"
<Desert.Piranha.21r...@excelforum-nospam.com> wrote in
message news:Desert.Piranha.21r...@excelforum-nospam.com...

[pas...@sdf.lonestar.org]

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

<pas...@sdf.lonestar.org> wrote in message
news:1137489778.5...@g47g2000cwa.googlegroups.com...

[pas...@sdf.lonestar.org]

> 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

[pas...@sdf.lonestar.org]

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

<pas...@sdf.lonestar.org> wrote in message
news:1137657203.4...@g14g2000cwa.googlegroups.com...

[pas...@sdf.lonestar.org]

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

---
Regards,
Norman

<pas...@sdf.lonestar.org> wrote in message
news:1137663436.5...@f14g2000cwb.googlegroups.com...

[NickHK]

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

NickHK

<pas...@sdf.lonestar.org> wrote in message
news:1137663436.5...@f14g2000cwb.googlegroups.com...