limit the number of files filesearch looks through

[jeremy]

Hey all,

I'm trying to use filesearch to look in a directory at certain files
and open them up. I'm getting success on my initial trial runs, but
when I go to looking in a folder across a Windows Network, it slows the
whole process down immensly. This folder can have over 1000 files, and
its just taking too long to get any good results. I really only want
to look at the past few files if possible. Is there a way to limit the
search to only the last 30 or so files that have been created? I've
tried to use the LastModified property and set it to
msoLastModifiedThisMonth, but it doesn't neccesarily make it any faster
of a process. Any thoughts?

Jeremy Heersink

 

[Jake Marx]

Hi Jeremy,

How long does it take? Is the folder slow when you open in it via Windows
Explorer? If the network share goes over a slow pipe or is located on an
overutilized server, there's probably not much you can do.

Does using the FileSystemObject work any faster for you? Try the code below
(replace path as needed and set a reference to "Microsoft Scripting Runtime"
library via Tools | References.

Sub test()
Dim fso As Scripting.FileSystemObject
Dim fol As Scripting.Folder
Dim fil As Scripting.File

Set fso = New Scripting.FileSystemObject

Set fol = fso.GetFolder("<<your path here>>")

Debug.Print "Count: " & fol.Files.Count

For Each fil In fol.Files
If fil.DateLastModified > DateSerial(2006, 7, 1) Then
Debug.Print fil.Name
End If
Next fil

Set fol = Nothing
Set fso = Nothing
End Sub

--
Regards,

Jake Marx
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]

 

[NickHK]

You do know that any file search mechanism is not guaranteed to return files
in any specific order.
As such what to hope to learn from the "first few files" ?

If you really want to find the 30 files with the latest LastModified date,
you will of course have to scan all files in that folder in order to
determine which ARE the oldest.
If you do not do it in code, whatever "helper" you choose will still have do
it and only report the results to you.

If you are looking for speed, the API FindFirstFile/FindNextFile are your
best bet.

NickHK

 

[Jeremy Heersink]

I just tried using the FileSystemObject, and it is much faster in my
testing. Thanks so much. I guess its time for a real world test now.
Thanks for your help.

Jeremy Heersink

Hi Jeremy,

How long does it take? Is the folder slow when you open in it via Windows
Explorer? If the network share goes over a slow pipe or is located on an
overutilized server, there's probably not much you can do.

Does using the FileSystemObject work any faster for you? Try the code below
(replace path as needed and set a reference to "Microsoft Scripting Runtime"
library via Tools | References.

Sub test()
Dim fso As Scripting.FileSystemObject
Dim fol As Scripting.Folder
Dim fil As Scripting.File

Set fso = New Scripting.FileSystemObject

Set fol = fso.GetFolder("<<your path here>>")

Debug.Print "Count: " & fol.Files.Count

For Each fil In fol.Files
If fil.DateLastModified > DateSerial(2006, 7, 1) Then
Debug.Print fil.Name
End If
Next fil

Set fol = Nothing
Set fso = Nothing
End Sub

--
Regards,

Jake Marx
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]

I'm trying to use filesearch to look in a directory at certain files
and open them up. I'm getting success on my initial trial runs, but
when I go to looking in a folder across a Windows Network, it slows
the whole process down immensly. This folder can have over 1000
files, and its just taking too long to get any good results. I
really only want to look at the past few files if possible. Is there
a way to limit the search to only the last 30 or so files that have
been created? I've tried to use the LastModified property and set it
to msoLastModifiedThisMonth, but it doesn't neccesarily make it any
faster of a process. Any thoughts?

Jeremy Heersink

 

[Dana DeLouis]

Hi. Not sure if this is helpful, but in the FileSystemObject, one can
search by "msoSortByLastModified."

I just copied part of a code to see if anything here might be helpful.

This example quickly found the last saved file.

However, "msoSortByLastModified" still has a bug, even in Excel 2003. It
won't work on the first call. Therefore, your program has to make a token
simple search first. Then, you can use it to search as below...

With Application.FileSearch

....etc bug workaround...
.NewSearch
.LookIn = sFileDir
.FileName = sType
If .Execute(SortBy:=msoSortByLastModified, _
SortOrder:=msoSortOrderDescending) > 0 Then
LastFileSaved = .FoundFiles(1)


Again, just thrown out to see if it's something you might want to try.
--
HTH. :>)
Dana DeLouis
Windows XP, Office 2003

I just tried using the FileSystemObject, and it is much faster in my
testing. Thanks so much. I guess its time for a real world test now.
Thanks for your help.

Jeremy Heersink

Hi Jeremy,

How long does it take? Is the folder slow when you open in it via
Windows
Explorer? If the network share goes over a slow pipe or is located on an
overutilized server, there's probably not much you can do.

Does using the FileSystemObject work any faster for you? Try the code
below
(replace path as needed and set a reference to "Microsoft Scripting
Runtime"
library via Tools | References.

Sub test()
Dim fso As Scripting.FileSystemObject
Dim fol As Scripting.Folder
Dim fil As Scripting.File

Set fso = New Scripting.FileSystemObject

Set fol = fso.GetFolder("<<your path here>>")

Debug.Print "Count: " & fol.Files.Count

For Each fil In fol.Files
If fil.DateLastModified > DateSerial(2006, 7, 1) Then
Debug.Print fil.Name
End If
Next fil

Set fol = Nothing
Set fso = Nothing
End Sub

--
Regards,

Jake Marx
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]

I'm trying to use filesearch to look in a directory at certain files
and open them up. I'm getting success on my initial trial runs, but
when I go to looking in a folder across a Windows Network, it slows
the whole process down immensly. This folder can have over 1000
files, and its just taking too long to get any good results. I
really only want to look at the past few files if possible. Is there
a way to limit the search to only the last 30 or so files that have
been created? I've tried to use the LastModified property and set it
to msoLastModifiedThisMonth, but it doesn't neccesarily make it any
faster of a process. Any thoughts?

Jeremy Heersink