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

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
--
Cheers,
Ryan


"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

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

--


Gary Keramidas
Excel 2003


"oldjay" wrote in message
...
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

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

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
.

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

Thanks that did the trick

"Dave Peterson" wrote:

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
.