FileSearch, my code is way too slow

[Guest]

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

 

[Jim Cone]

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

 

[Guest]

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?

 

[Guest]

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?

 

[Jim Cone]

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

 

[Guest]

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]

Jordan,

See if this does what you want...

'--------------------------------------
Sub LatestDrawingFile()
'Jim Cone - San Francisco, USA - May 19, 2005
'Requires a project reference to "Microsoft Scripting Runtime" (scrrun.dll)
'Required module level "Option Compare Text" statement
'Displays the last alphabetical file name in the strPath folder.

Dim objFSO As Scripting.FileSystemObject
Dim objFolder As Scripting.Folder
Dim objFile As Scripting.File
Dim arrNames() As String
Dim strPath As String
Dim lngNum As Long

' Specify the folder...
strPath = "C:\Program Files\Microsoft Office\Office\Library" '"V:\37\"

Set objFSO = New Scripting.FileSystemObject
Set objFolder = objFSO.GetFolder(strPath)
lngNum = objFolder.Files.Count
ReDim arrNames(1 To lngNum)
lngNum = 1

'Load array with all file names in folder.
For Each objFile In objFolder.Files
arrNames(lngNum) = objFile.Name
lngNum = lngNum + 1
Next 'objFile

'Call descending sort routine.
BubbleSort arrNames()
MsgBox arrNames(1)

Set objFSO = Nothing
Set objFolder = Nothing
Set objFile = Nothing
End Sub

'Modified Randy Birch sort routine.
'Sort in descending order.
Function BubbleSort(ByRef TempArray() As String)
Dim strTemp As String
Dim i As Long
Dim j As Long
Dim NoExchanges As Integer

Do
NoExchanges = True
For i = 1 To UBound(TempArray) - 1
j = i + 1
'Change "<" to ">" to sort ascending.
If TempArray(i) < TempArray(j) Then
NoExchanges = False
strTemp = TempArray(i)
TempArray(i) = TempArray(j)
TempArray(j) = strTemp
End If
Next 'i
Loop While Not NoExchanges

End Function
'-------------------------------------------

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

-snip-

 

[Guest]

Hey Jim,
Unfortunatly, your code is a bit over my head. The error I get is User
defined type is not defined...how do I get past that?

 

[Jim Cone]

Jordan,

To run the code provided, you must be using any version of Windows
released after Windows 95. Also, in the VBE, go to the Tools menu and
select "References". In the list of displayed files, find "Microsoft Scripting Runtime"
and check mark it.

That should take care of it.

Regards,
Jim Cone
San Francisco, USA

Hey Jim,
Unfortunatly, your code is a bit over my head. The error I get is User
defined type is not defined...how do I get past that?

-snip-

 

[Guest]

Hey Jim,
I may be able to get that to work for me...course, I may have questions
later, but thank you very much for the help.
Jordan

 

[Jim Cone]

Hi Jordan,

I found if the sort order is changed to ascending and you
pick the last file instead of the first that the speed improves.
On my machine, 900+ files went from 0.95 seconds to 0.25 seconds.

Regards,
Jim Cone
San Francisco, USA

 

[Guest]

Wow, thats a heck of a difference!!
Thanks Jim

Hi Jordan,

I found if the sort order is changed to ascending and you
pick the last file instead of the first that the speed improves.
On my machine, 900+ files went from 0.95 seconds to 0.25 seconds.

Regards,
Jim Cone
San Francisco, USA

 

[Guest]

Hey Jim,
I just wanted to let you know that I tried out that code you showed me and
it works GREAT!! I'm pretty excited about it.
Thank you for the help,
Jordan

 

[Jim Cone]

Jordan,
You are welcome.
Appreciate the feedback.
Jim Cone