How to replace the FileSearch application in Excel 2010

[jongova]

I have a macro that includes the FileSearch function, but in the 2010 excel version it doesn't work.
I would like your help to replace this FileSearch function.



Public Function SeekExcel(xdate As Date, folderpath As String) As String
Dim tfsearch As FileSearch
Dim filename, folderpath2, months(12) As String
months(1) = "Enero"
months(2) = "Febrero"
months(3) = "marzo"
months(4) = "Abril"
months(5) = "Mayo"
months(6) = "Junio"
months(7) = "Julio"
months(8) = "Agosto"
months(9) = "Septiembre"
months(10) = "Octubre"
months(11) = "Noviembre"
months(12) = "Diciembre"
folderpath2 = folderpath & CStr(Year(xdate)) & "\" & months(Month(xdate)) & "\" & CStr(Format(Day(xdate), "00")) & "\"
' finding the file name
Set tfsearch = Application.FileSearch
With tfsearch
.LookIn = folderpath2
.FileType = msoFileTypeExcelWorkbooks
.Execute
End With
For Each xfilename In tfsearch.FoundFiles
If (InStr(xfilename, "siopsurt") = 0) And (InStr(xfilename, "siopsur") > 0) Or (InStr(xfilename, "SIOPSURT") = 0) And (InStr(xfilename, "SIOPSUR") > 0) Then
filename = xfilename
End If
Next
SeekExcel = filename
End Function

[Kenneth Hobs]

http://answers.microsoft.com/en-us/office/forum/office_2010-customize/i-have-a-spreadsheet-which-uses/669118de-e3a5-4815-bb95-0a83eb6bf984

[ZVI]

Try this:


Public Function SeekExcel(XDate As Date, FolderPath As String) As String

Const SearchMask As String = "*siopsur*.xls*"
Const WrongMask As String = "*siopsurt*"
Const Months As String = "InSpain,Enero,Febrero,Marzo,Abril,Mayo,Junio,Julio,Agosto,Septiembre,Octubr e,Noviembre,Diciembre"

Dim File As String, Folder As String

If Mid(FolderPath, Len(FolderPath)) = "\" Then Folder = FolderPath Else Folder = FolderPath & "\"
Folder = Folder & Year(XDate) & "\" & Split(Months, ",")(Month(XDate)) & "\" & Format(Day(XDate), "00") & "\"

File = Dir(Folder & SearchMask)
Do While (Len(File))
If Not LCase(File) Like WrongMask Then
SeekExcel = Folder & File
Exit Do
End If
File = Dir()
Loop

End Function

[snb]

the same result without the need of array 'months'


folderpath2 = folderpath & Year(xdate) & "\" & monthname(Month(xdate)) & Format(Day(xdate), \\00\\ (file://\\00\\)))