On Error

[oldjay]

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

 

[Ryan H]

Is this what you are wanting?

Sub FindFile()

Dim QuoteNumber As String
Dim Quote1 As String
Dim Quote2 As String

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

Quote1 = "C:\Quotes\" & QuoteNumber & ".XLS"

On Error GoTo TryServer
Workbooks.Open filename:=Quote1
On Error GoTo 0

Exit Sub

TryServer:

Quote2 = "\\SERVER3\Jobs\Estimate1\NEW_QUOT1\" & QuoteNumber & ".XLS"
On Error GoTo CantFindQuotes
Workbooks.Open filename:=Quote2
On Error GoTo 0
Exit Sub

CantFindQuotes:

MsgBox "Can't find " & Quote1 & vbNewLine & vbNewLine & "or" & vbNewLine
& vbNewLine & Quote2, vbCritical

End Sub

 

[Gary Keramidas]

one way, you can adapt it to you specific needs.

Sub test()
Dim quotenumber As String
quotenumber = "1234"
If Len(Dir("C:\quotes\" & quotenumber & ".xls")) > 0 Then
MsgBox "open file on c drive"
Else
MsgBox "open file on server"
End If
End Sub

 

[Dave Peterson]

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]

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]

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]

Thanks that did the trick

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.