Excel 2003 Macro doesn't work in Excel 2007

[jjaw]

I’m using Windows 7 and I used a macro which worked totally fine under Excel 2003. Now, under Excel 2007 it does not run anymore but displays the following error message:

"Run-time error '445':
Object doesn't support this action.
The function of the macro is to open up all other Excel spreadsheets located in the same folder and copy data out of them into the spreadsheet in which the macro is stored.

When I click on "Debug" the Visual Basic Editor highlights the row which says: Set FilSrch = Application.FileSearch

Can you help me with this? I've searched for a solution for a long time but could not find anything which worked.

The following is the start of the macro code:


'MsgBox "This function not available in this version.", vbInformation, "Function Not Available"
'Exit Sub
'Sheets("LookUp").[a12] = Now
With Application
.ScreenUpdating = False
.StatusBar = "Counting source files ..."
End With
Set FilSrch = Application.FileSearchWith FilSrch
.NewSearch
.LookIn = ActiveWorkbook.Path
.FileType = msoFileTypeExcelWorkbooks
If .Execute > 0 Then
ReDim MyFilArray(.FoundFiles.Count)
For I = 1 To .FoundFiles.Count
If .FoundFiles(I) <> ActiveWorkbook.Path & "\" & ActiveWorkbook.Name Then
MyFilArray(I) = .FoundFiles(I)
End If
Next I
Else
MsgBox "There were no files found."
End If
End With

[Janos™]

I think that Filesearch method is not available in Excel 2007. There is a FileSearch class that you can add to your project. Instead of using Application.FileSearch, you reference FileSearch instead or you can also use VBScript. Hope that helps you out.

[Hollyn]

You can first try converting the Excel 2003 Macro to Excel 2007 and then try to run it. This might work. For that first you will have to open the file in Excel 2007 and save it in latest format. In Excel 2007 click on Office button and then click on Excel Option. Go to Trust Center > Trust Center Settings and in that click on Macro Settings. Disable All Macros with Notification. And then select Enable All Macros. Try again to open the files and see what output you are getting.

[Go!pee]

If you want to run old Macro then the first thing you have to do is save the file in Excel 97-2003 format. And then save the Macro with it. Both the version of Office suite use Visual Basic and it will work. The second thing you can do is first export the Macro from VB Editor. Then in your new file import them and test back. This will surely work. If still there is a issue then you have to try to export the file again properly. Verify the Macro is working by running it once in Excel 2003.