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" wrote in message
...
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