none
Object Doesn't Support This Action

    Question

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

    Friday, July 04, 2008 4:59 PM

All replies