View Full Version : count files extension

09-29-2010, 08:08 AM
hi all,

my boss ask me to count how many file in a folder/
when i search i got link http://www.vbaexpress.com/kb/getarticle.php?kb_id=247
but when i run it, i get bug
"object doesn't support this action" and when i press debug go to:
Set fs = Application.FileSearch

how to solve this, or maybe someone have others macro or tools to count how many files in a folder?

i use office 2010.

many thanks


Kenneth Hobs
09-29-2010, 08:23 AM
FileSearch was removed in 2007 which is more the pity.

Sub Test_CountFilesInFolder()
MsgBox CountFilesInFolder(ThisWorkbook.Path)
End Sub

Function CountFilesInFolder(pFolder As String) As Long
Rem Needs Reference: MicroSoft Scripting Runtime, scrrun.dll
Rem Instructions: http://support.microsoft.com/default.aspx?scid=kb;en-us;186118
Dim FSO As New FileSystemObject
Dim i As Long

With FSO
'Exit if parent folder does not exist.
If Not .FolderExists(pFolder) Then
MsgBox pFolder & " does not exist.", vbCritical, "Macro Ending"
Exit Function
End If

i = .GetFolder(ThisWorkbook.Path).Files.Count
End With
Set FSO = Nothing
CountFilesInFolder = i
End Function