Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
FileSearch, my code is way too slow
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
FileSearch, my code is way too slow
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
FileSearch, my code is way too slow
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
FileSearch, my code is way too slow
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" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
FileSearch, my code is way too slow
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SLOW Code... | Excel Programming | |||
Slow Code | Excel Programming | |||
Slow FileSearch Routine | Excel Programming | |||
Is this slow code? | Excel Programming | |||
FileSearch Code | Excel Programming |