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

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" <(E-Mail Removed)> wrote in message
news:d780f418-096c-4513-8b84-(E-Mail Removed)...
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

xl2007 dropped .FileSearch from its Library << it is not available
That's all i can tell you; sorry

"K" wrote:

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

On Nov 25, 11:29*am, JMay <J...@discussions.microsoft.com> wrote:
> xl2007 dropped .FileSearch from its Library << it is not available
> That's all i can tell you; sorry
>
>
>
> "K" wrote:
> > 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- Hide quoted text -
>
> - Show quoted text -

Thanks lot bob

Just small question with you bob that at the moment your code is
counting all excel files in a folder. But how can i specify it to
just count "xlsx" excel format files in folder.

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" <(E-Mail Removed)> wrote in message
> news:d780f418-096c-4513-8b84-(E-Mail Removed)...
> 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
>
>
>

Change

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

to

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

--
__________________________________
HTH

Bob

"K" <(E-Mail Removed)> wrote in message
news:0281e78f-9d5d-417e-9307-(E-Mail Removed)...
> Just small question with you bob that at the moment your code is
> counting all excel files in a folder. But how can i specify it to
> just count "xlsx" excel format files in folder.
>

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" <(E-Mail Removed)> wrote in message
news:A37EFFA9-8D81-4199-98EB-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:d780f418-096c-4513-8b84-(E-Mail Removed)...
>> 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
>>
>>
>>

On Nov 25, 12:39*pm, "Bob Phillips" <Bob...@somewhere.com> 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" <J...@discussions.microsoft.com> wrote in message
>
> news:A37EFFA9-8D81-4199-98EB-(E-Mail Removed)...
>
>
>
> > 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" <kamranr1...@yahoo.co.uk> wrote in message
> >>news:d780f418-096c-4513-8b84-(E-Mail Removed)....
> >> 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- Hide quoted text -
>
> - Show quoted text -

Thanks again Bob for all your help and time

One way:

Option Explicit
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 LCase(file.Name) Like "*.xlsx" Then
cnt = cnt + 1
End If
Next file

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

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

K wrote:
>
> Just small question with you bob that at the moment your code is
> counting all excel files in a folder. But how can i specify it to
> just count "xlsx" excel format files in folder.

--

Dave Peterson