> 
> I'm using Excel to track the number of files in a given network directory
> and using the DIR() function in a loop until it returns a zero-length
> string. Is there a quicker way to do this? I find that once the directory
> hits about 150 files, the count takes > 1 minute to perform, which is
> unfortunate because I want to count this on a minute by minute basis.
> 
> Suggestions will be tried, and much appreciated.


If Dir() is to slow, then what do you think of another DIR ... the one
of the DOS command-line, accessed through a Shell?

I've tried it and it is really faster than the VBA function. You could
test and adapt the following code to your own needs.
...certainly one of the most STUPID macros I've ever written, but it
works fine ;-)

'**************************************************
Function CountFiles(Path As String, Pattern As String) As Integer
  Dim TempDirTxt As String 'temporary dir file
  TempDirTxt = "c:\windows\temp\dir.txt" 'you can change it...
  ' VBA and Shell are asynchronous, thus...
  If Dir(TempDirTxt) <> "" Then Kill TempDirTxt
  Open TempDirTxt For Random As #1
  Close 1
  Shell "command.com /c dir/a-d/b " & Path & _
    "\" & Pattern & ">" & TempDirTxt
  ' Wait for the end of the DOS command :
  Do While FileLen(TempDirTxt) = 0
  Loop
  Application.ScreenUpdating = False
  Workbooks.Open TempDirTxt
  ' If [A1]="" : no files found
  If [A1] <> "" Then CountFiles = [A1].CurrentRegion.Rows.Count
  ActiveWorkbook.Close
  Application.ScreenUpdating = True
  Kill TempDirTxt
End Function

Sub Test()
  Dim TestPath As String
  TestPath = "c:\windows"
  nbfiles = CountFiles(TestPath, "*.*")
  MsgBox nbfiles & " files found in " & TestPath
End Sub
'**************************************************

Notes :

- Instead of opening TempDirTxt as a workbook and counting the filled
rows, you could perhaps open it as an external text file (instruction
"open"), search in the end of this file the string "XXX file(s)"
produced by dir/a-d (*no /b*), and then return the number XXX.
- dir/a-d/b/s counts also the files of the subdirectories.
- Made and tested in Excel 97 - I don't know if it works properly with
earlier versions.


Cordiales salutations,
Laurent Longre