Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    DIR function - sort by date (VBA Access 2003)

    Using Dir function to return the names of files in a selected folder. Is there a way to specify the order in which the files are returned for subsequent calls to the function? I want the first call to find the newest file, then subsequent calls to find the next newest, etc. until it has traversed the full list of files.

    Actually -- finding the newest file would be sufficient, because my program deletes each file after it has been processed. The newest file of the remaining files is what's necessary.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DIR function - sort by date (VBA Access 2003)

    Dir doesn't let you specify the order in which files are returned. You can use the Application.FileSearch object for this:

    Dim fs As FileSearch
    Dim i As Integer
    Set fs = Application.FileSearch
    With fs
    .LookIn = "C:My Documents"
    .Filename = "*.*"
    If .Execute(SortBy:=msoSortByLastModified, SortOrder:=msoSortOrderDescending) > 0 Then
    MsgBox "There were " & .FoundFiles.Count & " file(s) found."
    For i = 1 To .FoundFiles.Count
    MsgBox .FoundFiles(i)
    Next i
    Else
    MsgBox "There were no files found."
    End If
    End With

    Note: this won't work any more if/when you upgrade to Access 2007. In its infinite wisdom, Microsoft has decided that searching for files in VBA isn't necessary any more now that we have desktop search engines, so they removed FileSearch from Office 2007 VBA.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DIR function - sort by date (VBA Access 2003)

    Thanks, Hans. Worked like a charm.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  4. #4
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: DIR function - sort by date (VBA Access 2003)

    >> In its infinite wisdom, Microsoft has decided that searching for files in VBA isn't necessary any more

    Aarrrgh!!! <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>
    Regards
    Don

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DIR function - sort by date (VBA Access 2003)

    >Aarrrgh!!!
    I gloomily concur.
    Further I think that Hans ought not be allowed to make this the first post I see in my VBA forum digest on a Monday Morning (grin!)
    (Goes back to bed ...)

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DIR function - sort by date (VBA Access 2003)

    Huh? I didn't do it, no one saw me do it, there's no way you can prove anything!

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DIR function - sort by date (VBA Access 2003)

    >Huh? I didn't do it,
    I believe you.
    Thousands don't. And won't.
    Next time you don't do it, please don't do it first thing Monday morning.
    Leave it until December 1st when I'll have something pleasant in my life.

    (On a lighter note) It's difficult for me to comprehend the logic that allows Dear Old WordBasic to be effective in Word2003, but then decides to make Office 2007 not backward compatible in something related to useful FSO and the like.

    The bottom line: If it hadn't been for Don Wells's comment, I wouldn't have known how miserable I wasn't.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DIR function - sort by date (VBA Access 2003)

    You'll have something pleasant in your life on December 1st, and you'd like this to be ruined by me not doing something? I can't guarantee that I won't try not to do it...

    (I think I need a lie down now)

Posting Permissions

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