It's ok with me.

But if the user hits cancel when asked for the quotenumber, wouldn't you want
the code to stop?

Trim is built into excel's VBA. It should not cause an error.

So if you're getting an error message on this statement, it usually means that
you have an invalid reference in that workbook's project.

Open excel and your workbook
Open the VBE and select your workbook's project.
Then click on: Tools|References
Look for MISSING reference.

Uncheck that missing reference.

Then test your code. If it works ok, then go back to excel and save your
workbook.


oldjay wrote:

I got an error when I ran the routine at " If Trim(QuoteNumber) = "" Then"
it highlighted the "Trim " and said "Can't find project or library" It seems
to work when I commented it out.
Is that OK?

"Dave Peterson" wrote:

Option Explicit
Sub testme()

Dim TestStr As String
Dim Quote As String
Dim QuoteNumber As String
Dim PathsToTry As Variant
Dim pCtr As Long
Dim wkbk As Workbook
Dim myFileName As String
Dim FoundIt As Boolean

PathsToTry = Array("C:\quotes\", _
"\\SERVER3\Jobs\Estimate1\NEW_QUOT1\")

QuoteNumber = InputBox("...")

If Trim(QuoteNumber) = "" Then
Exit Sub 'user hit cancel
End If

FoundIt = False
For pCtr = LBound(PathsToTry) To UBound(PathsToTry)
myFileName = PathsToTry(pCtr) & QuoteNumber & ".xls"

TestStr = ""
On Error Resume Next
TestStr = Dir(myFileName)
On Error GoTo 0

If TestStr = "" Then
'wasn't found, keep looking
Else
FoundIt = True
Exit For
End If
Next pCtr

If FoundIt = False Then
MsgBox "It wasn't found!"
Exit Sub
End If

Set wkbk = Workbooks.Open(Filename:=myFileName)

MsgBox wkbk.FullName

End Sub


oldjay wrote:

I want to recall a file that if not on the C drive looks on the server

quotenumber = InputBox("Please enter QUOTE file name to recall", "X
Technologies LLC")

Quote = "C:\Quotes\" & quotenumber & ".XLS"

Quote = "\\SERVER3\Jobs\Estimate1\NEW_QUOT1\" & quotenumber & ".XLS"

Workbooks.Open Filename:=Quote

--

Dave Peterson
.


--

Dave Peterson