Number of files in each directory

[Guest]

Is there a macro that counts the number of files in a directory.

Thanks,
Mickey

 

[Guest]

Hi Mickey,

Here's one way to do it. It uses the FileSystemObject which can be very
useful when you work with the file system.

In your VBA project, go to Tools/References and select "Microsoft Scripting
Runtime".

You can use code such as this to count files:

Sub TestCount()
MsgBox NumFiles("c:\temp")
End Sub

Function NumFiles(FolderPath As String)
Dim FSO As Scripting.FileSystemObject
Dim MyFolder As Scripting.Folder

Set FSO = CreateObject("Scripting.FileSystemObject")
Set MyFolder = FSO.GetFolder(FolderPath)

NumFiles = MyFolder.Files.Count
End Function

It's also not difficult to make it work its way down through a folder's
sub-folders and sub-sub-folders etc. but the exact code for that would depend
on what you want to do with the numbers.

Regards.

Ed