YasserKhalil
07-25-2017, 03:10 PM
Hello everyone
I have found the following UDF that is supposed to count all files with specific extension at this link
https://www.excelguru.ca/content.php?140
'Author : Ken Puls (www.excelguru.ca (http://www.excelguru.ca/))'Function purpose: To count files in a directory. If a file extension is provided,
' then count only files of that type, otherwise return a count of all files.
Dim objFso As Object
Dim objFiles As Object
Dim objFile As Object
'Set Error Handling
On Error GoTo EarlyExit
'Create objects to get a count of files in the directory
Set objFso = CreateObject("Scripting.FileSystemObject")
Set objFiles = objFso.GetFolder(strDirectory).Files
'Count files (that match the extension if provided)
If strExt = "*.*" Then
CountFiles = objFiles.Count
Else
For Each objFile In objFiles
If UCase(Right(objFile.Path, (Len(objFile.Path) - InStrRev(objFile.Path, ".")))) = UCase(strExt) Then
CountFiles = CountFiles + 1
End If
Next objFile
End If
EarlyExit:
'Clean up
On Error Resume Next
Set objFile = Nothing
Set objFiles = Nothing
Set objFso = Nothing
On Error GoTo 0 End Function
Sub Test()'Author : Ken Puls (www.excelguru.ca (http://www.excelguru.ca/))
'Macro Purpose: Test the CountFiles function
Dim flDlg As FileDialog
Dim dblCount As Double
Set flDlg = Application.FileDialog(msoFileDialogFolderPicker)
flDlg.Show
dblCount = CountFiles(flDlg.SelectedItems(1))
Debug.Print dblCount End Sub
It is working well for all the files but when try to use specific extension it returned 0
I tried such a line
dblCount = CountFiles(flDlg.SelectedItems(1), "*.xls*")
Any idea?
I have found the following UDF that is supposed to count all files with specific extension at this link
https://www.excelguru.ca/content.php?140
'Author : Ken Puls (www.excelguru.ca (http://www.excelguru.ca/))'Function purpose: To count files in a directory. If a file extension is provided,
' then count only files of that type, otherwise return a count of all files.
Dim objFso As Object
Dim objFiles As Object
Dim objFile As Object
'Set Error Handling
On Error GoTo EarlyExit
'Create objects to get a count of files in the directory
Set objFso = CreateObject("Scripting.FileSystemObject")
Set objFiles = objFso.GetFolder(strDirectory).Files
'Count files (that match the extension if provided)
If strExt = "*.*" Then
CountFiles = objFiles.Count
Else
For Each objFile In objFiles
If UCase(Right(objFile.Path, (Len(objFile.Path) - InStrRev(objFile.Path, ".")))) = UCase(strExt) Then
CountFiles = CountFiles + 1
End If
Next objFile
End If
EarlyExit:
'Clean up
On Error Resume Next
Set objFile = Nothing
Set objFiles = Nothing
Set objFso = Nothing
On Error GoTo 0 End Function
Sub Test()'Author : Ken Puls (www.excelguru.ca (http://www.excelguru.ca/))
'Macro Purpose: Test the CountFiles function
Dim flDlg As FileDialog
Dim dblCount As Double
Set flDlg = Application.FileDialog(msoFileDialogFolderPicker)
flDlg.Show
dblCount = CountFiles(flDlg.SelectedItems(1))
Debug.Print dblCount End Sub
It is working well for all the files but when try to use specific extension it returned 0
I tried such a line
dblCount = CountFiles(flDlg.SelectedItems(1), "*.xls*")
Any idea?