count files in folder

[kamal1jacq]

Sub Demo()
Call CountfilesInFolder("C:\Users\kamal.j.kumar\Documents\CDP", "*.xls*")
End Sub

Hi,
In the above macro, I get the following compile error: sub or function not defined. Why ?

 

[VBA Geek]

Do you have a function called CountFilesInFolder?

 

[Rick Rothstein]

Here is a function that you can call to get the file count

[table="width: 500"]
[tr]
	[td]Function FileCount(ByVal Path As String, WildcardFileName As String) As Long
  Dim Filename As String
  If Right(Path, 1) <> "\" Then Path = Path & "\"
  Filename = Dir(Path & WildcardFileName)
  Do While Len(Filename)
    FileCount = FileCount + 1
    Filename = Dir()
  Loop
End Function[/td]
[/tr]
[/table]

Simply pass it the path to the files and the wildcard filename (same order as you used in your CountfilesInFolder). Here is a demo using the path and wildcard filename that your original posting used...

[table="width: 500"]
[tr]
	[td]Sub Demo()
  MsgBox FileCount("C:\Users\kamal.j.kumar\Documents\CDP", "*.xls*")
End Sub[/td]
[/tr]
[/table]