Counting files in a folder

F

Frank Kabel

Hi
try something like the following:


Sub test()
Dim i As Long
With Application.FileSearch
.NewSearch
.LookIn = "C:\Temp\"
.SearchSubFolders = False
.MatchTextExactly = False
.FileType = msoFileTypeAllFiles
If .Execute(msoSortOrderDescending) > 0 Then
MsgBox "There were " & .FoundFiles.Count & " file(s) found."
Else
MsgBox "There were no files found."
End If
End With
End Sub
 
C

Chip Pearson

Try something like

Dim FSO As Object
Set FSO = CreateObject("Scripting.FileSystemObject")
Debug.Print FSO.GetFolder("C:\Temp").Files.Count


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
R

Ron de Bruin

Hi Chip

Is there also a easy way to count only the Excel files for example
with your example.
 
C

Chip Pearson

Ron,
Is there also a easy way to count only the Excel files for
Click to expand...
example

You'd have to loop through the Files collection and check the
extension.

Dim FSO As Object
Dim F As Object
Dim N As Long
Set FSO = CreateObject("Scripting.FileSystemObject")
For Each F In FSO.GetFolder("H:\Temp").Files
If StrComp(Right$(F.Name, 4), ".xls") = 0 Then
N = N + 1
End If
Next F
Debug.Print N



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
J

Jake Marx

Ron,

I think you'll have to loop for that:

Public Function glCountXLFilesInFolder(rsPath _
As String) As Long
Dim FSO As Object
Dim fil As Object
Dim lCount As Long

Set FSO = CreateObject("Scripting.FileSystemObject")
For Each fil In FSO.GetFolder(rsPath).Files
If StrComp(Right$(fil.Name, 4), ".xls", _
vbTextCompare) = 0 Then lCount = lCount + 1
Next fil
glCountXLFilesInFolder = lCount
Set FSO = Nothing
End Function

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]
 
R

Ron de Bruin

ThanksChip

I was hoping for a other(faster) way<g>

I always use the code that Frank posted
with .FileType = msoFileTypeExcelWorkbooks
 
H

Hawki

This was precisely what I needed (much better than what I
had come up with)...thanks a bunch.
 
Top