Running old Excel Macro in Excel 2007 - Error 445: Object doesn't support this action

[Legacy 165086]

Hi All,

I have recently upgraded to Excel 2007 from Excel 2000 and am trying to run a macro. I get the following error:

"Error 445: Object doesn't support this action"

From a bit of searching I found that 'FileSearch' is not Supported in Excel 2007 onwards. I did a search in my code and came across an instance of 'FileSearch' being used:

With Application.FileSearch
.NewSearch
.Filename = PlanName
.LookIn = dir
.Execute
If .FoundFiles.Count = 0 Then
MsgBox "Plan File does not exist or Directory is incorrect"
ActiveWorkbook.Close Savechanges:=False
Exit Sub
End If
End With

Can someone point me in the direction of a resource that would help me to convert this code into something that would work with Excel 2007?

Thanks

 

[shg]

If PlanName is a fully-qualified path and file name, then

    If Len(Dir(PlanName)) = 0 Then
        MsgBox PlanFile & " does not exist"
        Exit Sub
    End If

 

[shg]

Oops:

    If Len(Dir(PlanName)) = 0 Then
        MsgBox [COLOR=red]PlanName[/COLOR] & " does not exist"
        Exit Sub
    End If

 

[Legacy 165086]

Oops:

    If Len(Dir(PlanName)) = 0 Then
        MsgBox [COLOR=red]PlanName[/COLOR] & " does not exist"
        Exit Sub
    End If

Just tried your code. Comes back with "File Error - Array Expected", highlighting the part of the code that says 'dir'.

Any ideas?

 

[shg]

What's in PlanName?

 

[Legacy 165086]

PlanName = "plan_template_" & CStr(product) & ".xls"

 

[shg]

I mean, what is the total value of the string?

 

[Legacy 165086]

In the end I used the following code to fix the issue:

Function ReportFileStatus(PlanName)
Dim fso As New FileSystemObject
If fso.FileExists(PlanName) = False Then
MsgBox "Plan File does not exist or Directory is incorrect"
ActiveWorkbook.Close Savechanges:=False
Exit Sub
End If
End Function

Thanks for your help shg4421!

 

[shg]

I've lost the bubble as to what the problem is.

You can't have an Exit Sub in a Function, and your function doesn't return a value. What is it supposed to do?

You don't need the FleSystemObject object to tell if a file exists, as I showed, and the Dir function doesn't take an array, it takes a string with either just a file name (if in the current directory) or a fully-qualified path and name.

What's broken?