Code works, but is slow

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

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

Thanks Norman.

Were getting close, however I'm getting a runtime error on line:

Workbooks.Open Filename:=sPath & sFileName

I checked, and the filename is correct.

I tried it with just the name, with and without the .xls extension, also by placing a \ slash in front of the filename, but no dice. What am I missing?

Thanks,
EMoe

I'd say check again--either the path or the value in that cell.

You may want to put:
msgbox sPath & sFileName

before the line that blows up so you can see what's happening.

EMoe wrote:
>
> Thanks Norman.
>
> Were getting close, however I'm getting a runtime error on line:
>
> Workbooks.Open Filename:=sPath & sFileName
>
> I checked, and the filename is correct.
>
> I tried it with just the name, with and without the .xls extension,
> also by placing a \ slash in front of the filename, but no dice. What
> am I missing?
>
> 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

--

Dave Peterson

Hi Emoe,

In addition to Dave's suggestion, try changing:

>> If Not Right(sFileName, 4) <> ".xls" Then

to:

If Not LCase(Right(sFileName, 4)) <> ".xls" Then

---
Regards,
Norman



"EMoe" <[email protected]> wrote in message
news:[email protected]...
>
> Thanks Norman.
>
> Were getting close, however I'm getting a runtime error on line:
>
> Workbooks.Open Filename:=sPath & sFileName
>
> I checked, and the filename is correct.
>
> I tried it with just the name, with and without the .xls extension,
> also by placing a \ slash in front of the filename, but no dice. What
> am I missing?
>
> 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
>

this should work

Workbooks.Open Filename:="C:\folder\file.xls"

or
Workbooks.Open Filename:=Range("g8")
where g8 has this
C:\folder\file.xls

or
Workbooks.Open Filename:="C:\folder\" & range("g8") & ".xls"
where g8 just has
file

Merry Xmas

--
Don Guillett
SalesAid Software
[email protected]
"EMoe" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

Thanks for the replies,

I will try to work with the codes tommorrow. It's time for me to go home now from work.
Will post results tommorrow.

Thanks,
EMoe

Hello Programmers & Merry Christmas!!!

Thanks Norman, Dave & Don!

You were all right about the codes. I checked several times the path & file name, and they were right. But code still gave me an error.

The problem was not with the spelling of the file name, but with the file name itself.

File Name: #4 FINISH MILL DEC.2005.xls

The dot between DEC & 2005 caused excel a problem. It was considering 2005 to be a file extension. I removed the dot, and every thing went smoothly.

Sorry, I should have provided the file name in the very beginning. Well 1 issue resolved, and one to go. Please see my post VBA to open .mdb file.

Thanks again,
Regards,
EMoe