I am using this bit of code to open a workbook where the path and
workbook name is in cell Y8
Workbooks.Open Range("Y8").Value
ActiveWorkbook.RunAutoMacros xlAutoOpen

However I want to do 2 things
I need that if the workbook is already open the selected workbook is
just activated, I must not have the option to open the workbook again
and overwrite the changes made to the open workbook.
If the file is not found, I get an error message and the macro must
then end. The debug option must not appear.

Help will be appreciated

Dim wb As Workbook
Dim wbShortName As String
wbShortName = Right(Range("Y8"),Len(Range("Y8")) - _
InStrRev(Range("Y8"),Application.PathSeparator))
On Error Resume Next
Set wb = Workbooks(wbShortName)
If wb is Nothing Then
If Len(Dir(Range("Y8"))) 0 Then
Set wb = Workbooks.Open(Range("Y8")
Else
Msgbox "File Does Not Exist, Program will now Abort"
End If
Else
wb.Activate 'Not sure if this is what you asked for or not
'What workbook did you mean by "Selected"
End If

Let me know what else you want this to do.
--
Charles Chickering

"A good example is twice the value of good advice."


" wrote:

I am using this bit of code to open a workbook where the path and
workbook name is in cell Y8
Workbooks.Open Range("Y8").Value
ActiveWorkbook.RunAutoMacros xlAutoOpen

However I want to do 2 things
I need that if the workbook is already open the selected workbook is
just activated, I must not have the option to open the workbook again
and overwrite the changes made to the open workbook.
If the file is not found, I get an error message and the macro must
then end. The debug option must not appear.

Help will be appreciated

Brian,

"InStrRev" was available starting XL XP I think (or XL 2000) so if you have
earlier, you'll have to find the "\" before the filename some other way.
Ideally, you'll create separate functions that have the logic of determining
whether a file exists, or is open.

Sub test()
Dim wb_name As String
Dim wb_full_name As String
Dim test_wb As Workbook

wb_full_name = Range("Y8")
wb_name = Mid$(wb_full_name, InStrRev(wb_full_name, "\") + 1)

If Dir(wb_full_name) = "" Then
MsgBox "File doesn't exist"
Exit Sub
End If

On Error Resume Next
Workbooks(wb_name).Activate
If Err.Description < "" Then
Workbooks.Open wb_full_name
End If
On Error GoTo 0
If ActiveWorkbook.Name = wb_name Then
ActiveWorkbook.RunAutoMacros xlAutoOpen
Else
'just in case I didn't think of everything above
MsgBox "Something went wrong, but I'm not sure what!"
End If
End Sub

hth,

Doug

wrote in message
oups.com...
I am using this bit of code to open a workbook where the path and
workbook name is in cell Y8
Workbooks.Open Range("Y8").Value
ActiveWorkbook.RunAutoMacros xlAutoOpen

However I want to do 2 things
I need that if the workbook is already open the selected workbook is
just activated, I must not have the option to open the workbook again
and overwrite the changes made to the open workbook.
If the file is not found, I get an error message and the macro must
then end. The debug option must not appear.

Help will be appreciated

I have tried Doug Glancy's solution and it works perfectly.

Thank you to all who replied.

I am using this code on XL 2000 as well and

InStrRev(wb_full_name, "\") + 1)

Does not work on XL 2000. Do you have an alternative?

maybe it's that final close paren that's causing the error.

wrote:

I am using this code on XL 2000 as well and

InStrRev(wb_full_name, "\") + 1)

Does not work on XL 2000. Do you have an alternative?

--

Dave Peterson

Sorry Dave, if you look further up in the topic you will see that this
is part of a routine.

The full line is

wb_name = Mid$(wb_full_name, InStrRev(wb_full_name, "\") + 1)

What I am doing here is finding the name of the workbook in the full
path name which appears in cell Y8.

I believe the code InStrRev does not work in Excel 2000 and this is
causing my problem. What I need is an alternative to find the workbook
name.

InStrRev was added in xl2k. It won't work on xl97 (or earlier).

Maybe adding some message boxes (or debug.prints) to see what is in wb_full_name
would help you debug the problem.

Dim wb_name As String
Dim wb_Full_Name As String

wb_Full_Name = ThisWorkbook.FullName
Debug.Print "Full name --" & wb_Full_Name
wb_name = Mid$(wb_Full_Name, InStrRev(wb_Full_Name, "\") + 1)
Debug.Print "name --" & wb_name

Returned:

Full name --C:\My Documents\excel\Book1.xls
name --Book1.xls




wrote:

Sorry Dave, if you look further up in the topic you will see that this
is part of a routine.

The full line is

wb_name = Mid$(wb_full_name, InStrRev(wb_full_name, "\") + 1)

What I am doing here is finding the name of the workbook in the full
path name which appears in cell Y8.

I believe the code InStrRev does not work in Excel 2000 and this is
causing my problem. What I need is an alternative to find the workbook
name.

--

Dave Peterson