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.

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

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