Application.FileSearch

[kclong]

I know Excel 2007 no longer has the Application.FileSearch available. I'm confused looking at different posts on how to fix it. I just want to open the last file modified in a certain directory. Here's how it looked for MS Excel 2003:

With Application.FileSearch
.LookIn = "U:\Daily Files\"
.Filename = "*.xls"
If .Execute(msoSortByLastModified, msoSortOrderDescending) > 0 Then
Workbooks.Open .FoundFiles(1), ReadOnly:=True
Else
MsgBox "There were no files found."
End If
End With

How do I get this to work in 2007?

 

[Domenic]

Try...

[font=Verdana][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Sub[/color] test()

    [color=darkblue]Dim[/color] strPath [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] strFile [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] MyFile [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] MyDate [color=darkblue]As[/color] Date
    [color=darkblue]Dim[/color] LMD [color=darkblue]As[/color] Date

    strPath = "U:\Daily Files\"
    
    [color=darkblue]If[/color] Right(strPath, 1) <> "\" [color=darkblue]Then[/color] strPath = strPath & "\"
    
    strFile = Dir(strPath & "*.xls")
    
    [color=darkblue]Do[/color] [color=darkblue]While[/color] Len(strFile) > 0
        LMD = FileDateTime(strPath & strFile)
        [color=darkblue]If[/color] LMD > MyDate [color=darkblue]Then[/color]
            MyFile = strPath & strFile
        [color=darkblue]End[/color] [color=darkblue]If[/color]
        strFile = Dir
    [color=darkblue]Loop[/color]
    
    [color=darkblue]If[/color] LMD > 0 [color=darkblue]Then[/color]
        Workbooks.Open MyFile
    [color=darkblue]Else[/color]
        MsgBox "No files found..."
    [color=darkblue]End[/color] [color=darkblue]If[/color]
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]

 

[kclong]

This works great. Thanks. One follow up question. The macro says to look for a .xls file. What if I have .xls, xlsm, xlsx, etc. files and I'm looking for the most recent file regardless of extension. How would you modify it? Thanks again.

 

[GTO]

If you step thru the code, you'll see that it picks up .xls* as is.