Cannot save file after upgrade to 2007 VBA

Question

I have a sub which I call to save a file

Sub SaveToFile()

    maxr = Worksheets("List").Range("H1")

    Worksheets("List").Range("G1:AE" & maxr).Copy

Part of the code which is failing after an upgrade to 2007 is:

    With Application.FileSearch
        .LookIn = "Q:\Planning Tools\Reports\"
        .Filename = "Plan_" & ThisSaveTime & ".xls"

I receive a runtime error '445' object doesn't support this action, the code then continues below:

        If .Execute > 0 Then 'Existing Workbook
            Application.Workbooks.Open ("Q:\Planning Tools\Reports\Plan_" & ThisSaveTime & ".xls")
            ActiveWorkbook.Worksheets.Add
            ActiveWorkbook.Sheets("Sheet1").Select
            ActiveWorkbook.Sheets("Sheet1").Name = ThisPlanSaveName
        Else 'No existing Workbook, so add one
            Workbooks.Add 1
            ActiveWorkbook.Sheets("Sheet1").Select
            ActiveWorkbook.Sheets("Sheet1").Name = ThisPlanSaveName
        End If
   End With

    ......

End Sub

I am not sure which action is failing but can anyone see why?

Thanks

Answer 1

Microsoft has removed FileSearch from the Excel 2007 Object Model

There are many possible replacements like Dir and using the FileSystemObject

try these links:

• Mr Excel: Replacement class for FileSearch
• MSDN Communities: Application.FileSearch in Excel 2007
• Ozgrid Forums - Rplacement for FileSearch
• Ozgrid Forums - Application.FileSearch Replacement in Excel 2007
• Execl-IT.com: Replacement for FileSearch