sort-by-date in 2007

lijon · #1 (**permalink**) 02-23-11, 15:18

Hi all,
Since xls2007 doesn't include application.filesearch it seems we're going back to using Dir (path, filters) to work with files in folders. I can figure out how to use Dir instead of filesearch below, but I'm stuck on how to make sure I get the most recent version of the file (it's saved daily and called "Baskets 2011-mm-dd.xls").

Any ideas how to sort by date if we can't use filesearch? Many thanks to all!

Code:

With Application.FileSearch
    .NewSearch
    .LookIn = strFolderName

    .filename = "Baskets*.xls"    
' the filenames include the date such as "Baskets 2011-02-23.xls"

    .Execute SortBy:=msoSortBydate
    .LookIn = strFolderName
    .filename = "Baskets*.xls"


        If .Execute(msoSortByLastModified, msoSortOrderDescending) > 0 Then
        strFileName = .FoundFiles(1)
        Application.Workbooks.Open strFileName
        End If

End With

Colin Legg · #2 (**permalink**) 02-24-11, 06:49

Hi,

Quote:

I can figure out how to use Dir instead of filesearch below,

Okay, that's a good start. So simply loop through the files in the folder, check if each file name begins with "Baskets" and, if it does, check if the date at the end of the file name is larger than other files you have checked (store the largest date in a variable and update it as you loop through the files). Once all the files have been checked, open the file using the variable that contains the largest date.

lijon · #3 (**permalink**) 02-24-11, 10:43

Colin that's pretty clever! I'll go try it! Thanks much!

lijon · #4 (**permalink**) 02-24-11, 14:18

Here's what I replaced it with, just to close the loop and show others.

Code:

ChDir "W:\Myfolder\"

Dim strf As String
strf = Dir("Baskets*.xls")
        
        Do While strf <> ""
            myfile = strf      'this captures the very last filename
            strf = Dir
        Loop                  ' don't fully understand, but it loops to the next file
    
Application.Workbooks.Open myfile

Internet Services

Web Development

Digital Marketing

Consumer Tech