Can anyone tell me a small loop of code to find the total
number of files in a dir ?

You could use the FileSystemObject:

Sub test()
Set fso = CreateObject("Scripting.FileSystemObject")
Set folder = fso.GetFolder("D:\Temp\Test")
Set Files = folder.Files

' Get the Count
Debug.Print Files.Count

Set folder = Nothing
Set Files = Nothing
Set fso = Nothing

End Sub

Cheers

Leyton

-----Original Message-----
Can anyone tell me a small loop of code to find the total
number of files in a dir ?
.

Neil:
Look up the FoundFiles Object in Excel VBA Help.
Some excellent examples there.

hth,
Jim Feaver
"Neil" wrote in message
...
Can anyone tell me a small loop of code to find the total
number of files in a dir ?

Neil,

Here is a simple UDF.

Function FileCount(FolderName As String, _
Optional FileFilter As String = "*", _
Optional FileTypes As Long = 1, _
Optional SubFolders As Boolean = False) As Long
With Application.FileSearch
.NewSearch
.LookIn = FolderName
.SearchSubFolders = SubFolders
.Filename = FileFilter
.MatchTextExactly = True
.FileType = FileTypes
.Execute
FileCount = .FoundFiles.Count
End With
End Function

Use like so

FileCount("C:\myTest") - all files in a directory
FileCount("C:\myTest",,,True) - all files in a directory, including
subfolders
FileCount("C:\myTest","Test") - all files in a directory with 'test' in the
name
FileCount("C:\myTest",,4) - all Excel workbooks in a directory

This can be called from a worksheet or VBA, VBA has the advantage of
allowing the msoFileType Enum for file types.


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Neil" wrote in message
...
Can anyone tell me a small loop of code to find the total
number of files in a dir ?