Found Files

[Stuart]

I have a folder with an ever expanding number of files named as follows

oldOrders001.xls
oldOrders002.xls
oldOrders003.xls
oldOrders004.xls
oldOrders005.xls
~
oldOrders095.xls

etc........

will the following piece of code always find the very last alphabetically
listed file, I only have win98 to test it on and am curious to know what
will happen on other platforms such as XP

With Application.FileSearch
.LookIn = "C:\shopmain\orders\done"
.SearchSubFolders = False
.FileName = "oldOrders*.xls"
.Execute
lastFile = .FoundFiles(.FoundFiles.Count)
End With

 

[Guest]

I don't think you can guarantee the sort order unless you specify it, but
that is not hard to do - this example is from the help file for FoundFiles:

Use the Execute method to begin the file search and update the FoundFiles
object. The following example searches the My Documents folder for all files
whose names begin with "Cmd" and displays the name and location of each file
that's found. The example also sorts the returned files in ascending
alphabetic order by file name.

Set fs = Application.FileSearch
With fs
.LookIn = "C:\My Documents"
.FileName = "cmd*"
If .Execute(SortBy:=msoSortbyFileName, _
SortOrder:=msoSortOrderAscending) > 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

 

[Stuart]

Brilliant, that's exactly what I wanted!

I don't think you can guarantee the sort order unless you specify it, but
that is not hard to do - this example is from the help file for FoundFiles:

Use the Execute method to begin the file search and update the FoundFiles
object. The following example searches the My Documents folder for all files
whose names begin with "Cmd" and displays the name and location of each file
that's found. The example also sorts the returned files in ascending
alphabetic order by file name.

Set fs = Application.FileSearch
With fs
.LookIn = "C:\My Documents"
.FileName = "cmd*"
If .Execute(SortBy:=msoSortbyFileName, _
SortOrder:=msoSortOrderAscending) > 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