Object Doesn't Support This Action

Fri, 04 Jul 2008 16:59:47 Z

Whenever I run the following section of code, I get the run-time error '445': Object doesn't support this action. It is refering to using the FileSearch method with the Application object. I know that in Excel 2003, this method did support this action, but now that I'm trying to do it with Excel 2007, it is giving me this error. I'm just wondering if there is a new method that has taken the place of FileSearch, and if there is, what that method would be.

Dim cp As Workbook
Dim cp2 As Long
Dim cp3 As Integer
With Application.FileSearch
.NewSearch
.LookIn = "C:\Users\D\Desktop\2008\VBA Test\CP"
.Filename = "*.xls"
If .Execute(SortBy:=msoSortByLastModified, SortOrder:=msoSortOrderDescending) > 0 Then
For i = .FoundFiles.Count To 1 Step -1
Set cp = Workbooks.Open(.FoundFiles(i))
cp.Activate
Range("B9:B574").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("Z10"), Unique:=True
Range("Z10").Activate
cp3 = 0
For cp2 = 1 To 100
Do
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(1, 0))
If ActiveCell.Offset(2, 0).Value = 0 Then
cp3 = cp3 + ActiveCell.Row - 10
Exit For
Else
cp3 = cp3 - 1
ActiveCell.Offset(2, 0).Select
End If
Next cp2
Workbooks("VBA Test.xls").Activate
Worksheets("Sheet1").Activate
If i = 1 Then
Cells(1, 2).Value = cp3
ElseIf i = 2 Then
Cells(1, 3).Value = cp3
ElseIf i = 3 Then
Cells(1, 4).Value = cp3
ElseIf i = 4 Then
Cells(1, 5).Value = cp3
ElseIf i = 5 Then
Cells(1, 6).Value = cp3
ElseIf i = 6 Then
Cells(1, 7).Value = cp3
ElseIf i = 7 Then
Cells(1, 8).Value = cp3
ElseIf i = 8 Then
Cells(1, 9).Value = cp3
ElseIf i = 9 Then
Cells(1, 10).Value = cp3
ElseIf i = 10 Then
Cells(1, 11).Value = cp3
ElseIf i = 11 Then
Cells(1, 12).Value = cp3
ElseIf i = 12 Then
Cells(1, 13).Value = cp3
End If
Next i
End If
End With

Thanks.

Object Doesn't Support This Action

Sat, 05 Jul 2008 02:56:23 Z

Filesearch method is not available in Excel 2007. Here are some alternatives

http://www.mrexcel.com/forum/showthread.php?t=268046

http://www.ozgrid.com/forum/showthread.php?t=71409

Cheers

Shasur