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

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



--
Regards
Frank Kabel
Frankfurt, Germany


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

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



"Hawki" wrote in message
...
Is there an easy way to count files in a given folder?
TIA...

Hi Chip

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Chip Pearson" wrote in message ...
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



"Hawki" wrote in message
...
Is there an easy way to count files in a given folder?
TIA...

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



"Ron de Bruin" wrote in message
...
Hi Chip

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Chip Pearson" wrote in message
...
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



"Hawki" wrote in
message
...
Is there an easy way to count files in a given folder?
TIA...

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 wrote:
Hi Chip

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



"Chip Pearson" wrote in message
...
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



"Hawki" wrote in message
...
Is there an easy way to count files in a given folder?
TIA...

ThanksChip

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

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Chip Pearson" wrote in message ...
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



"Ron de Bruin" wrote in message
...
Hi Chip

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Chip Pearson" wrote in message
...
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



"Hawki" wrote in
message
...
Is there an easy way to count files in a given folder?
TIA...

This was precisely what I needed (much better than what I
had come up with)...thanks a bunch.
-----Original Message-----
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



"Ron de Bruin" wrote in message
...
Hi Chip

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Chip Pearson" wrote in message
...
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



"Hawki" wrote in
message
...
Is there an easy way to count files in a given
folder?
TIA...






.