Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2008
    Posts
    8

    Thumbs up Unanswered: sort-by-date in 2007

    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

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi,
    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.

  3. #3
    Join Date
    Mar 2008
    Posts
    8
    Colin that's pretty clever! I'll go try it! Thanks much!

  4. #4
    Join Date
    Mar 2008
    Posts
    8
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •