Application.FileSearch doesn't work

[tommy1234]

Hello
In the past i wrote a code that search for many .xls files in a specific path.
The code i used contain the "FileSearch" and from what i understand the command doesn't exists in Excel 2007 and that's the reason for my code error.
Can some help me to change the code that it could work on excel 2007.
[VBA]
Set MySearch = Application.FileSearch
With MySearch
.NewSearch
.LookIn = filepath
.filename = "*" & LastVersion & ".xls"
.SearchSubFolders = True

If .Execute > 0 Then
For i = 1 To .FoundFiles.Count ' run all over the files in the last version
myFile = .FoundFiles(i) ' get file name & open it.
On Error GoTo OpenHandler

Workbooks.Open filename:=myFile
Worksheets(source_worksheet).Activate
Worksheets(source_worksheet).Columns("A:" & end_column).Select
Selection.RemoveSubtotal
If i = 1 Then
Workbooks(Workbooks.Count).Worksheets(worksheet_num).Range("A1:" & end_column & "1").Copy
ThisWorkbook.Worksheets(dest_worksheet).Range("A1:" & end_column & "1").PasteSpecial
End If
paste_row_EMC = copyRows(PaymentCenter, paste_row_EMC, worksheet_num, dest_worksheet, end_column)
Next i
Else
MsgBox "The file in this version does'nt exist Or maybe a problem with the path"
'ActiveWorkbook.Worksheets("Sorted_By_Project_drilldown").Protect Password:="MIKA01"
CalcEMC_Table = False
Exit Function
End If
End With
[/VBA]

Thank you

[Jan Karel Pieterse]

Maybe this gets you started:

Sub FileSearchReplacement()
    Dim sFolder As String
    Dim sFileSpec As String
    Dim sFiles() As String
    Dim sCurFile As String
    Dim lCt As Long
    sFolder = "c:\data\"
    sFileSpec = "*.xl*"
    ReDim sFiles(1 To 1)
    lCt = 1
    sFiles(1) = Dir(sFolder & sFileSpec)
    Do
        sCurFile = Dir()
        If Len(sCurFile) > 0 Then
            lCt = lCt + 1
            ReDim Preserve sFiles(1 To lCt)
            sFiles(lCt) = sCurFile
        End If
    Loop Until Len(sCurFile) = 0
    MsgBox UBound(sFiles) & " files found."
End Sub