FileSearch, my code is way too slow

Hey Everyone,
I have a drive that holds about 20 folders, each of which contains anywhere
from 10-1000 tiff files. I have a program that, given a list of numbers,
goes in and finds the appropriate file and prints the drawing into the active
excel workbook and then prints off a hard copy. below, I have included a
portion of the code that I am using, i can include more if you like, but i
didn't want this to be too long. This seems to be the main hang-up area, it
just searches very slowly. Is there a bit of code that would search through
these files faster?

With Application.FileSearch
.NewSearch
.LookIn = "V:\" & foldername
.FileType = msoFileTypeAllFiles
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
If StrComp(.FoundFiles(i), revpath) > 0 Then 'when code has gone
one part too far
If i = 1 Then 'i.e. if drawing even exist
MsgBox "Drawing " & myCell & " is not listed.", , "Sorry"
Exit For
Else
x = i - 1
'Print to excel file and so on

Jordan,

In my experience, the Scripting Runtime - File System Object runs
faster than FileSearch. The following example is from the help file...

'--------------------------
Function ReportFileStatus(filespec)
Dim fso, msg
Set fso = CreateObject("Scripting.FileSystemObject")
If (fso.FileExists(filespec)) Then
msg = filespec & " - exists."
Else
msg = filespec & " - doesn't exist."
End If
ReportFileStatus = msg
End Function

'Call it like this...

Sub IsItThere()
MsgBox ReportFileStatus("C:\Program Files\Creative\Audio\ReadMe.txt")
End Sub
'-----------------------------------

Jim Cone
San Francisco, USA



"Jordan" <Jordan@discussions.microsoft.com> wrote in message
news:5896802D-9C2F-46A7-9A97-0C4BB1FE0C99@microsoft.com...
> Hey Everyone,
> I have a drive that holds about 20 folders, each of which contains anywhere
> from 10-1000 tiff files. I have a program that, given a list of numbers,
> goes in and finds the appropriate file and prints the drawing into the active
> excel workbook and then prints off a hard copy. below, I have included a
> portion of the code that I am using, i can include more if you like, but i
> didn't want this to be too long. This seems to be the main hang-up area, it
> just searches very slowly. Is there a bit of code that would search through
> these files faster?
>
> With Application.FileSearch
> .NewSearch
> .LookIn = "V:\" & foldername
> .FileType = msoFileTypeAllFiles
> If .Execute() > 0 Then
> For i = 1 To .FoundFiles.Count
> If StrComp(.FoundFiles(i), revpath) > 0 Then 'when code has gone
> one part too far
> If i = 1 Then 'i.e. if drawing even exist
> MsgBox "Drawing " & myCell & " is not listed.", , "Sorry"
> Exit For
> Else
> x = i - 1
> 'Print to excel file and so on

Hello Everyone, and Jim,
I am trying out the method Jim gave me, so far it looks like it may be my
answer, it will take me a bit of time to revise the code to fit it. In the
mean time, are there any other ways of searching for files that are faster
than the method I am using?

"Jim Cone" wrote:

> Jordan,
>
> In my experience, the Scripting Runtime - File System Object runs
> faster than FileSearch. The following example is from the help file...
>
> '--------------------------
> Function ReportFileStatus(filespec)
> Dim fso, msg
> Set fso = CreateObject("Scripting.FileSystemObject")
> If (fso.FileExists(filespec)) Then
> msg = filespec & " - exists."
> Else
> msg = filespec & " - doesn't exist."
> End If
> ReportFileStatus = msg
> End Function
>
> 'Call it like this...
>
> Sub IsItThere()
> MsgBox ReportFileStatus("C:\Program Files\Creative\Audio\ReadMe.txt")
> End Sub
> '-----------------------------------
>
> Jim Cone
> San Francisco, USA
>
>
>
> "Jordan" <Jordan@discussions.microsoft.com> wrote in message
> news:5896802D-9C2F-46A7-9A97-0C4BB1FE0C99@microsoft.com...
> > Hey Everyone,
> > I have a drive that holds about 20 folders, each of which contains anywhere
> > from 10-1000 tiff files. I have a program that, given a list of numbers,
> > goes in and finds the appropriate file and prints the drawing into the active
> > excel workbook and then prints off a hard copy. below, I have included a
> > portion of the code that I am using, i can include more if you like, but i
> > didn't want this to be too long. This seems to be the main hang-up area, it
> > just searches very slowly. Is there a bit of code that would search through
> > these files faster?
> >
> > With Application.FileSearch
> > .NewSearch
> > .LookIn = "V:\" & foldername
> > .FileType = msoFileTypeAllFiles
> > If .Execute() > 0 Then
> > For i = 1 To .FoundFiles.Count
> > If StrComp(.FoundFiles(i), revpath) > 0 Then 'when code has gone
> > one part too far
> > If i = 1 Then 'i.e. if drawing even exist
> > MsgBox "Drawing " & myCell & " is not listed.", , "Sorry"
> > Exit For
> > Else
> > x = i - 1
> > 'Print to excel file and so on
>
>

Another thing that I thought I should mention:
These files are all drawings, many of which have multiple revisions and
multiple pages. So, for example, 37 may have the following drawings in it:

37001-03501-1
37001-03501-2
37001-03501-3
37001-03501A1
37001-03501A2
37001-03501A3
37001-03501B1
37001-03501B2
37001-03501B3

In my program This would tell me that for part 37001-03501 there are 3 pages
and that the latest revision is revision B. So in the code I use now, I tell
it to search for Part Num 37001-03501Z, and then when it has gone to far, I
tell it to come back one file so that it lands on the most current revision,
B. So, if I use Jims code, how do I tell it to find V:\37\37001-03501##.tif
where the pounds are veriables?

"Jordan" wrote:

> Hello Everyone, and Jim,
> I am trying out the method Jim gave me, so far it looks like it may be my
> answer, it will take me a bit of time to revise the code to fit it. In the
> mean time, are there any other ways of searching for files that are faster
> than the method I am using?
>
> "Jim Cone" wrote:
>
> > Jordan,
> >
> > In my experience, the Scripting Runtime - File System Object runs
> > faster than FileSearch. The following example is from the help file...
> >
> > '--------------------------
> > Function ReportFileStatus(filespec)
> > Dim fso, msg
> > Set fso = CreateObject("Scripting.FileSystemObject")
> > If (fso.FileExists(filespec)) Then
> > msg = filespec & " - exists."
> > Else
> > msg = filespec & " - doesn't exist."
> > End If
> > ReportFileStatus = msg
> > End Function
> >
> > 'Call it like this...
> >
> > Sub IsItThere()
> > MsgBox ReportFileStatus("C:\Program Files\Creative\Audio\ReadMe.txt")
> > End Sub
> > '-----------------------------------
> >
> > Jim Cone
> > San Francisco, USA
> >
> >
> >
> > "Jordan" <Jordan@discussions.microsoft.com> wrote in message
> > news:5896802D-9C2F-46A7-9A97-0C4BB1FE0C99@microsoft.com...
> > > Hey Everyone,
> > > I have a drive that holds about 20 folders, each of which contains anywhere
> > > from 10-1000 tiff files. I have a program that, given a list of numbers,
> > > goes in and finds the appropriate file and prints the drawing into the active
> > > excel workbook and then prints off a hard copy. below, I have included a
> > > portion of the code that I am using, i can include more if you like, but i
> > > didn't want this to be too long. This seems to be the main hang-up area, it
> > > just searches very slowly. Is there a bit of code that would search through
> > > these files faster?
> > >
> > > With Application.FileSearch
> > > .NewSearch
> > > .LookIn = "V:\" & foldername
> > > .FileType = msoFileTypeAllFiles
> > > If .Execute() > 0 Then
> > > For i = 1 To .FoundFiles.Count
> > > If StrComp(.FoundFiles(i), revpath) > 0 Then 'when code has gone
> > > one part too far
> > > If i = 1 Then 'i.e. if drawing even exist
> > > MsgBox "Drawing " & myCell & " is not listed.", , "Sorry"
> > > Exit For
> > > Else
> > > x = i - 1
> > > 'Print to excel file and so on
> >
> >

Jordan,

Are you looking for the actual file name or
just trying to determine the latest revision number?

Is the latest revision by Alpha only "B" or
must the latest revision include the numeric suffix "B3"?

Would the file that was created most recently or
the file that was most recently modified do the job for you?


Regards,
Jim Cone
San Francisco, USA




"Jordan" <Jordan@discussions.microsoft.com> wrote in message
news:397385CE-6B88-48F3-8349-2B807BC3C33D@microsoft.com...
> Another thing that I thought I should mention:
> These files are all drawings, many of which have multiple revisions and
> multiple pages. So, for example, 37 may have the following drawings in it:
>
> 37001-03501-1
> 37001-03501-2
> 37001-03501-3
> 37001-03501A1
> 37001-03501A2
> 37001-03501A3
> 37001-03501B1
> 37001-03501B2
> 37001-03501B3
>
> In my program This would tell me that for part 37001-03501 there are 3 pages
> and that the latest revision is revision B. So in the code I use now, I tell
> it to search for Part Num 37001-03501Z, and then when it has gone to far, I
> tell it to come back one file so that it lands on the most current revision,
> B. So, if I use Jims code, how do I tell it to find V:\37\37001-03501##.tif
> where the pounds are veriables?
>
> "Jordan" wrote:
>
> > Hello Everyone, and Jim,
> > I am trying out the method Jim gave me, so far it looks like it may be my
> > answer, it will take me a bit of time to revise the code to fit it. In the
> > mean time, are there any other ways of searching for files that are faster
> > than the method I am using?
> >
> > "Jim Cone" wrote:
> >
> > > Jordan,
> > >
> > > In my experience, the Scripting Runtime - File System Object runs
> > > faster than FileSearch. The following example is from the help file...
> > >
> > > '--------------------------
> > > Function ReportFileStatus(filespec)
> > > Dim fso, msg
> > > Set fso = CreateObject("Scripting.FileSystemObject")
> > > If (fso.FileExists(filespec)) Then
> > > msg = filespec & " - exists."
> > > Else
> > > msg = filespec & " - doesn't exist."
> > > End If
> > > ReportFileStatus = msg
> > > End Function
> > >
> > > 'Call it like this...
> > >
> > > Sub IsItThere()
> > > MsgBox ReportFileStatus("C:\Program Files\Creative\Audio\ReadMe.txt")
> > > End Sub
> > > '-----------------------------------
> > >
> > > Jim Cone
> > > San Francisco, USA
> > >
> > >
> > >
> > > "Jordan" <Jordan@discussions.microsoft.com> wrote in message
> > > news:5896802D-9C2F-46A7-9A97-0C4BB1FE0C99@microsoft.com...
> > > > Hey Everyone,
> > > > I have a drive that holds about 20 folders, each of which contains anywhere
> > > > from 10-1000 tiff files. I have a program that, given a list of numbers,
> > > > goes in and finds the appropriate file and prints the drawing into the active
> > > > excel workbook and then prints off a hard copy. below, I have included a
> > > > portion of the code that I am using, i can include more if you like, but i
> > > > didn't want this to be too long. This seems to be the main hang-up area, it
> > > > just searches very slowly. Is there a bit of code that would search through
> > > > these files faster?
> > > >
> > > > With Application.FileSearch
> > > > .NewSearch
> > > > .LookIn = "V:\" & foldername
> > > > .FileType = msoFileTypeAllFiles
> > > > If .Execute() > 0 Then
> > > > For i = 1 To .FoundFiles.Count
> > > > If StrComp(.FoundFiles(i), revpath) > 0 Then 'when code has gone
> > > > one part too far
> > > > If i = 1 Then 'i.e. if drawing even exist
> > > > MsgBox "Drawing " & myCell & " is not listed.", , "Sorry"
> > > > Exit For
> > > > Else
> > > > x = i - 1
> > > > 'Print to excel file and so on
> > >
> > >

Hey Jim,
To answer the first question, what we are actually looking for is the latest
revision and the number of pages. The revision level is the alpha character
only, the 3 behind the alpha character signifies that there are three pages
to the drawing. So if I were wanting to print the latest drawing revision
for the example i gave before, i would actually want to print:
37001-03501B1,
37001-03501B2, and
37001-03501B3

so that I would get all three pages of the latest revision
so if i type in 37001-03501, the actual file path i need it to return would
include the last page of the latest revision, which would look like this:
V:\37\37001-03501B3.tif

I like your idea about creation date and last modified date, unfortunatly,
this will not work because many of the files were created or added to the
files out of sequence.

Thanks for helping me out with this,
Jordan

"Jim Cone" wrote:

> Jordan,
>
> Are you looking for the actual file name or
> just trying to determine the latest revision number?
>
> Is the latest revision by Alpha only "B" or
> must the latest revision include the numeric suffix "B3"?
>
> Would the file that was created most recently or
> the file that was most recently modified do the job for you?
>
>
> Regards,
> Jim Cone
> San Francisco, USA
>
>
>
>
> "Jordan" <Jordan@discussions.microsoft.com> wrote in message
> news:397385CE-6B88-48F3-8349-2B807BC3C33D@microsoft.com...
> > Another thing that I thought I should mention:
> > These files are all drawings, many of which have multiple revisions and
> > multiple pages. So, for example, 37 may have the following drawings in it:
> >
> > 37001-03501-1
> > 37001-03501-2
> > 37001-03501-3
> > 37001-03501A1
> > 37001-03501A2
> > 37001-03501A3
> > 37001-03501B1
> > 37001-03501B2
> > 37001-03501B3
> >
> > In my program This would tell me that for part 37001-03501 there are 3 pages
> > and that the latest revision is revision B. So in the code I use now, I tell
> > it to search for Part Num 37001-03501Z, and then when it has gone to far, I
> > tell it to come back one file so that it lands on the most current revision,
> > B. So, if I use Jims code, how do I tell it to find V:\37\37001-03501##.tif
> > where the pounds are veriables?
> >
> > "Jordan" wrote:
> >
> > > Hello Everyone, and Jim,
> > > I am trying out the method Jim gave me, so far it looks like it may be my
> > > answer, it will take me a bit of time to revise the code to fit it. In the
> > > mean time, are there any other ways of searching for files that are faster
> > > than the method I am using?
> > >
> > > "Jim Cone" wrote:
> > >
> > > > Jordan,
> > > >
> > > > In my experience, the Scripting Runtime - File System Object runs
> > > > faster than FileSearch. The following example is from the help file...
> > > >
> > > > '--------------------------
> > > > Function ReportFileStatus(filespec)
> > > > Dim fso, msg
> > > > Set fso = CreateObject("Scripting.FileSystemObject")
> > > > If (fso.FileExists(filespec)) Then
> > > > msg = filespec & " - exists."
> > > > Else
> > > > msg = filespec & " - doesn't exist."
> > > > End If
> > > > ReportFileStatus = msg
> > > > End Function
> > > >
> > > > 'Call it like this...
> > > >
> > > > Sub IsItThere()
> > > > MsgBox ReportFileStatus("C:\Program Files\Creative\Audio\ReadMe.txt")
> > > > End Sub
> > > > '-----------------------------------
> > > >
> > > > Jim Cone
> > > > San Francisco, USA
> > > >
> > > >
> > > >
> > > > "Jordan" <Jordan@discussions.microsoft.com> wrote in message
> > > > news:5896802D-9C2F-46A7-9A97-0C4BB1FE0C99@microsoft.com...
> > > > > Hey Everyone,
> > > > > I have a drive that holds about 20 folders, each of which contains anywhere
> > > > > from 10-1000 tiff files. I have a program that, given a list of numbers,
> > > > > goes in and finds the appropriate file and prints the drawing into the active
> > > > > excel workbook and then prints off a hard copy. below, I have included a
> > > > > portion of the code that I am using, i can include more if you like, but i
> > > > > didn't want this to be too long. This seems to be the main hang-up area, it
> > > > > just searches very slowly. Is there a bit of code that would search through
> > > > > these files faster?
> > > > >
> > > > > With Application.FileSearch
> > > > > .NewSearch
> > > > > .LookIn = "V:\" & foldername
> > > > > .FileType = msoFileTypeAllFiles
> > > > > If .Execute() > 0 Then
> > > > > For i = 1 To .FoundFiles.Count
> > > > > If StrComp(.FoundFiles(i), revpath) > 0 Then 'when code has gone
> > > > > one part too far
> > > > > If i = 1 Then 'i.e. if drawing even exist
> > > > > MsgBox "Drawing " & myCell & " is not listed.", , "Sorry"
> > > > > Exit For
> > > > > Else
> > > > > x = i - 1
> > > > > 'Print to excel file and so on
> > > >
> > > >
>