Hi EMoe,

If you know the folder name, why search?

Try:

'=============
Public Sub AGetSheet()
Dim sFileName As String
Const sPath As String = _
"I:\PLANT\LAB\Lab Tech Stuff\2005 FINISH MILL SHEETS\"

sFileName = Range("P12").Value

If Not Right(sFileName, 4) < ".xls" Then
sFileName = sFileName & ".xls"
End If

Workbooks.Open Filename:=sPath & sFileName

End Sub
'<<=============


---
Regards,
Norman

"EMoe" wrote in message
...

Hello again,

Here is a code that works fine, however, because of the number of files
it has to scan (or look through) it takes as much as 2 minutes to open
the file it's looking for. Below I provide the code and the path it
takes.

Is there a way to modify this code to look in a certain folder on the
drive to speed up its search process.

CODE:
Sub AGetSheet()
'subroutine to open a filename from a cell, looking in the specified
drive
With Application.FileSearch
NewSearch
LookIn = "I:\"
SearchSubFolders = True
Filename = Range("P12")
If .Execute 0 Then
Workbooks.Open .FoundFiles(1)
End If
End With
End Sub

PATH:
I:\PLANT\LAB\Lab Tech Stuff\2005 FINISH MILL SHEETS\#4 FINISH MILL
2005

In the cell P12, I have #4 Finish Mill 2005, which excel does find it,
but again its not fast enough; the drive is very large. Can the code
reference excel to look in, say the 2005 FINISH MILL SHEETS (which is
the folder where the file is) to shorten the search?

Sorry so long,
Thanks,
EMoe


--
EMoe
------------------------------------------------------------------------
EMoe's Profile:
http://www.excelforum.com/member.php...o&userid=23183
View this thread: http://www.excelforum.com/showthread...hreadid=495925