File Count

  • Thread starter Thread starter Tom
  • Start date Start date
Tom,

Here's one way to do it. You can use a function like the one below and call
it from some other procedure while passing the folder path:

Public Function fnFilecount(folderspec As String)
On Error GoTo Err_fnFilecount

Dim fs As Object
Dim f As Object

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(folderspec)

MsgBox f.Files.Count

Exit_fnFilecount:
On Error Resume Next
fs = Nothing
f = Nothing
Exit Function

Err_fnFilecount:
MsgBox "Error #: " & vbTab & vbTab & Err.Number & vbCrLf _
& "Description: " & vbTab & Err.Description
Resume Exit_fnFilecount

End Function
 
Oops...

Forgot to replace "Msgbox" with "fnFilecount". Here's a better version.

Public Function fnFilecount(folderspec As String) As Integer
On Error GoTo Err_fnFilecount

Dim fs As Object
Dim f As Object

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(folderspec)

fnFilecount = f.Files.Count

Exit_fnFilecount:
On Error Resume Next
fs = Nothing
f = Nothing
Exit Function

Err_fnFilecount:
MsgBox "Error #: " & vbTab & vbTab & Err.Number & vbCrLf _
& "Description: " & vbTab & Err.Description
Resume Exit_fnFilecount

End Function
 
Thanks Mike that works nicely - If however I wanted to
refine the count by either just counting a file range of
a certain suffix, i.e. *.doc or commencing with a certain
prefix i.e. test*.jpg, how could this be done>

TIA
Tom
 
Tom,

This alternative method seemed to work in a quick test...

Public Function fnFilecount(folderspec As String, filespec As String) As
Integer
On Error GoTo Err_fnFilecount

'folderspec is the folder path and needs to include the last "\".
'
'filespec is the search string for the file, including the file type.
For example,
'you can choose *.txt, somefile.*, etc. ? for single-character
wildcard.
'
'The function is currently written to find files having their archive,
hidden, read
'only or zero attributes set. You can mix and match these as you like.
Files having
'the *system* attribute have been deliberately excluded. I was getting
an error with
'this setting and didn't have time to research/fix it...and you may not
even need it
'anyway.

Dim strNextFile As String

'Retrieve the first entry.
strNextFile = Dir(folderspec & filespec, vbArchive + vbHidden + vbNormal
_
+ vbReadOnly)
Do While strNextFile <> ""
'Ignore the current directory and the encompassing directory.
If strNextFile <> "." And strNextFile <> ".." Then
'Use bitwise comparison to make sure strNextFile is not a
directory.
If (GetAttr(folderspec & strNextFile) And vbDirectory) <>
vbDirectory Then
fnFilecount = fnFilecount + 1
End If
End If
'Get next file.
strNextFile = Dir
Loop

Exit_fnFilecount:
On Error Resume Next
strNextFile = ""
folderspec = ""
filespec = ""
Exit Function

Err_fnFilecount:
MsgBox "Error #: " & vbTab & vbTab & Err.Number & vbCrLf _
& "Description: " & vbTab & Err.Description
Resume Exit_fnFilecount

End Function
 
Back
Top