Counting files in a folder

[Hawki]

Is there an easy way to count files in a given folder?
TIA...

 

[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

 

[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

 

[Ron de Bruin]

Hi Chip

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

 

[Chip Pearson]

Ron,

Is there also a easy way to count only the Excel files for

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

 

[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]

 

[Ron de Bruin]

ThanksChip

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

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

 

[Hawki]

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