+ Reply to Thread
Results 1 to 14 of 14

FileSearch, my code is way too slow

  1. #1
    Jordan
    Guest

    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. #2
    Jim Cone
    Guest

    Re: 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" <[email protected]> wrote in message
    news:[email protected]...
    > 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. #3
    Jordan
    Guest

    Re: 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" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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. #4
    Jordan
    Guest

    Re: 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" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > 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. #5
    Jim Cone
    Guest

    Re: 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" <[email protected]> wrote in message
    news:[email protected]...
    > 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" <[email protected]> wrote in message
    > > > news:[email protected]...
    > > > > 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. #6
    Jordan
    Guest

    Re: 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" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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" <[email protected]> wrote in message
    > > > > news:[email protected]...
    > > > > > 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
    > > > >
    > > > >

    >


  7. #7
    Jim Cone
    Guest

    Re: FileSearch, my code is way too slow

    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
    '-------------------------------------------



    "Jordan" <[email protected]> wrote in message
    news:[email protected]...
    > 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
    > >

    -snip-

  8. #8
    Jordan
    Guest

    Re: FileSearch, my code is way too slow

    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" wrote:

    > 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
    > '-------------------------------------------
    >
    >
    >
    > "Jordan" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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
    > > >

    > -snip-
    >


  9. #9
    Jim Cone
    Guest

    Re: FileSearch, my code is way too slow

    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



    "Jordan" <[email protected]> wrote in message
    news:[email protected]...
    > 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" wrote:
    >
    > > 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 MSKB code...
    > > '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
    > > '-------------------------------------------

    -snip-

  10. #10
    Jordan
    Guest

    Re: FileSearch, my code is way too slow

    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" wrote:

    > 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
    >
    >
    >
    > "Jordan" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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" wrote:
    > >
    > > > 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 MSKB code...
    > > > '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
    > > > '-------------------------------------------

    > -snip-
    >


  11. #11
    Jim Cone
    Guest

    Re: FileSearch, my code is way too slow

    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


    "Jordan" <[email protected]> wrote in message
    news:[email protected]...
    > 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


  12. #12
    Jordan
    Guest

    Re: FileSearch, my code is way too slow

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

    "Jim Cone" wrote:

    > 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
    >
    >
    > "Jordan" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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

    >


  13. #13
    Jordan
    Guest

    Re: FileSearch, my code is way too slow

    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" wrote:

    > 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
    >
    >
    > "Jordan" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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

    >


  14. #14
    Jim Cone
    Guest

    Re: FileSearch, my code is way too slow

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



    "Jordan" <[email protected]> wrote in message
    news:[email protected]...
    > 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" wrote:



    > > 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





    > > "Jordan" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > 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



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1