PDA

View Full Version : Application.FileSearch doesn't work



tommy1234
01-03-2010, 11:01 PM
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
01-04-2010, 01:22 AM
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