Code works, but is slow

[EMoe]

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

 

[Norman Jones]

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

 

[EMoe]

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. Wha
am I missing?

Thanks,
EMo

 

[Dave Peterson]

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.

 

[Norman Jones]

Hi Emoe,

In addition to Dave's suggestion, try changing:

to:

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

 

[Don Guillett]

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

 

[EMoe]

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

 

[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 th
file name itself.

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

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

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

Thanks again,
Regards,
EMo