MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Application.Filesearch more opts for .LastModified


Posted by John Moore on February 23, 2001 8:49 AM

I would like to use the filesearch to create
an array of names. I can get it to look in a directory,
filter by name, but the options presented in the help
file for .LastModified are limited to:
today
yesterday
this week
last week
... etc
but I would like to filter by a user-specified date range.

FYI, I am using Excel '97

Thanks in advance
-John


Posted by Dax on February 23, 2001 1:44 PM

John,
One way you could do this is to use the FileSystemObject. E.g. this piece of code will return a collection with the full path name of each file matching the criteria:-


Sub SearchFiles()
Dim DateFirst As Date, DateLast As Date
Dim Col As New Collection
Dim fs As Object, fil As Object

Set fs = CreateObjec("Scripting.FilesystemObject")
DateFirst = #2/1/2001#
DateLast = #2/20/2001#
For Each fil In fs.GetFolder("C:\My Documents").Files
If fil.DateLastModified >= DateFirst And _
fil.DateLastModified <= DateLast Then
Col.Add fil.Path
End If
Next
End Sub

This doesn't search subfolders. If you need that then you have to use a more complicated procedure. Let me know if you do.

Regards,
Dax.

Posted by John Moore on February 23, 2001 2:37 PM

Dax,

I'm afraid that I do need to search in subfolders.

FYI, here is what I have so far(lifted directly
from the VB help file, with a few changes).It fulfils my
requirements, with the exception of filtering by
users-specified date.
Thanks for your response
-John

SearchPath = "C:\My Documents"

Set fs = Application.FileSearch
With fs
.LookIn = SearchPath
.FileName = "*.wk1"
.SearchSubFolders = True
If .Execute > 0 Then
For i = 1 To .FoundFiles.Count
DataFile = .FoundFiles(i)
Call OpenFile(DataFile)
Set DataBook = ActiveWorkbook
Call AuditFile(DataBook)
DataBook.Close
Next i
Else
MsgBox "There were no files found."
End If
End With

Posted by John Moore on February 23, 2001 3:41 PM

Dax,
I think I have found an alternate solution.
Setting .LastModified to msoLastModifiedThisMonth
will limit the number of files I have to search through
to an acceptable number.
Each file I will search through has a cell with
a date stamp. which I will then reference to narrow
the search further to the user-specified limits.

Thanks again for the response. I will post again
if the alternate solution does not do what I need.
-John