Excel 2003 Macro doesn't run under Excel 2007

Microsoft Office excel 2007
April 14, 2011 at 22:13:36
Specs: Windows 7
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:

Sub UpdateTable()
Dim X As Range, I As Integer, J As Integer, RecNo As Integer, CopyVal As Variant
Dim SourceBk As Worksheet, DestBk As Worksheet, StartRow As Integer, IndRange As Range
Dim ServRange As Range, SrcOpen As Boolean, SourceName As String
Dim FilSrch As Object, MyFilArray() As String

'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.FileSearch
With FilSrch
.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
MsgBox "There were no files found."
End If
End With

See More: Excel 2003 Macro doesnt run under Excel 2007

Report •

April 17, 2011 at 16:27:20
As far as I know, Application.FileSearch is not available in 2007.

Searching for filesearch replacement turns up lots of hits with various workarounds, such as this:


Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •
Related Solutions

Ask Question