|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:
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"
'Sheets("LookUp").[a12] = Now
.ScreenUpdating = False
.StatusBar = "Counting source files ..."
Set FilSrch = Application.FileSearch
.LookIn = ActiveWorkbook.Path
.FileType = msoFileTypeExcelWorkbooks
If .Execute > 0 Then
For I = 1 To .FoundFiles.Count
If .FoundFiles(I) <> ActiveWorkbook.Path & "\" & ActiveWorkbook.Name Then
MyFilArray(I) = .FoundFiles(I)
MsgBox "There were no files found."