DIR function – sort by date (VBA Access 2003)

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

Thanks, Hans. Worked like a charm.

Reply | Quote

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

Aarrrgh!!!

Reply | Quote

>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 …)

Reply | Quote

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

Reply | Quote

>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.

Reply | Quote

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)

Reply | Quote