Thanks Bob;
FWIW:
Last Comment - When comparing the Count (in Cell A1) to my actual 2007 Data
files, I found the code to produce 1 greater than the actual count. Had to
(on last line) use Range("A1").Value = cnt - 1



"Bob Phillips" wrote:

Like so

Sub test()
Dim foldername As String
Dim FSO As Object
Dim fldr As Object
Dim file As Object
Dim cnt As Long

foldername = "C:\Data"
Set FSO = CreateObject("Scripting.FilesystemObject")
Set fldr = FSO.GetFolder(foldername)
For Each file In fldr.Files

If Mid$(file.Name, InStrRev(file.Name, ".") + 1) = "xlsm" Then

cnt = cnt + 1
End If
Next file

Set file = Nothing
Set fldr = Nothing
Set FSO = Nothing

Range("A1").Value = cnt
End Sub



--
__________________________________
HTH

Bob

"JMay" wrote in message
...
Bob, how would one search by extension type, say for example -- only
.xlsm ?


"Bob Phillips" wrote:

FileSearch has been dropped for Excel 2007.

Try

Sub test()
Dim foldername As String
Dim FSO As Object
Dim fldr As Object
Dim file As Object
Dim cnt As Long

foldername = "C:\Data"
Set FSO = CreateObject("Scripting.FilesystemObject")
Set fldr = FSO.GetFolder(foldername)
For Each file In fldr.Files

If file.Type Like "*Microsoft Office Excel*" Then

cnt = cnt + 1
End If
Next file

Set file = Nothing
Set fldr = Nothing
Set FSO = Nothing

Range("A1").Value = cnt
End Sub



--
__________________________________
HTH

Bob

"K" wrote in message
...
Hi all, I am working in Excel 2007 and I want macro to count all
"xlsx" fromat files in a folder and put that total count number in
Range("A1"). I found macro (see below) but I am getting error message
on line "With Application.FileSearch" saying "Run-time error 445 -
Object doesn't support this action" Please can any friend can help me
on this.

Sub test()
Dim foldername As String
foldername = "C:\Data"
With Application.FileSearch
..NewSearch
..LookIn = foldername
..SearchSubFolders = False
..FileType = msoFileTypeExcelWorkbooks
If .Execute() 0 Then
MsgBox .FoundFiles.Count & " Excel files were found"
Else
MsgBox "There were no files found."
End If
End With
End Sub