Checking for Open Workbook
 

I've put a button on a spreadsheet that opens another workbook, works
fine if the other workbook isn't already open.

What I need to do, is to modify this so that it checks to see if this
workbook is already open, and if it is pop-up a message box saying the
workbook is already open, otherwise it can just go ahead and open the
closed workbook...

TIA for any suggestions.

Checking for Open Workbook
 

this is a better approach:

Sub Tester11()
Dim wkbk As Workbook
On Error Resume Next
Set wkbk = Workbooks("BigData1.xls")
On Error GoTo 0
If wkbk Is Nothing Then
Set wkbk = Workbooks.Open("C:\Data\BigData1.xls")
End If
wkbk.Activate

End Sub

--
Regards,
Tom Ogilvy


"sbharbour" wrote in message
...
Tom:

This worked great! Thanks! One more question... Is there a way to
turn off the message box tell you that the Worksheet is already open
and just go to the workbook.activate routine???

TIA

sbharbour

On Error Resume Next
Workbooks.Open "C:\Myfolder\MyBook.xls"
On Error Goto 0
Workbooks("MyBook.xls").Activate

--
Regards,
Tom Ogilvy


sbharbour wrote in message
...
I've put a button on a spreadsheet that opens another workbook,
works
fine if the other workbook isn't already open.

What I need to do, is to modify this so that it checks to see if
this
workbook is already open, and if it is pop-up a message box saying
the
workbook is already open, otherwise it can just go ahead and open
the
closed workbook...

TIA for any suggestions.

Checking for Open Workbook
 

The box still comes up saying the workbook is already open...

Checking for Open Workbook
 

With this code??? -- Not for me.

Sub Tester11()
Dim wkbk As Workbook
On Error Resume Next
Set wkbk = Workbooks("BigData1.xls")
On Error GoTo 0
If wkbk Is Nothing Then
Set wkbk = Workbooks.Open("C:\Data\BigData1.xls")
End If
wkbk.Activate

End Sub

I can't believe that - it doesn't try to open the workbook if it is already
open.

Sub Tester11()
Dim wkbk As Workbook
Dim sName as String, sPath
sName = "BigData1.xls"
sPath = "C:\Data\"
On Error Resume Next
Set wkbk = Workbooks(sName)
On Error GoTo 0
If wkbk Is Nothing Then
Set wkbk = Workbooks.Open(sPath & sName)
End If
wkbk.Activate

End Sub

The above minimizes the posibility of typing in different names.

--
Regards,
Tom Ogilvy




"sbharbour" wrote in message
...
The box still comes up saying the workbook is already open...

Checking for Open Workbook
 

It does try to open the book that is already open, but what I want to do
is suppress the message box telling that it's already open reopening
will cause changes to be discarded, do you want to reopen Yes No

???

Checking for Open Workbook
 

The original code did that - the new code I posted does not.

If you suppress that message, then I am not sure whether it re-opens it or
not. Generally suppressing the message takes the default action which
appears to be to reopen the workbook - which could result in loss of data.

To suppress messages

Application.DisplayAlerts = False

' code that would cause the alert

Application.DisplayAlerts = True

But like I said, the second set of code does not try to open the workbook if
it is already open.

--
Regards,
Tom Ogilvy


"sbharbour" wrote in message
...
It does try to open the book that is already open, but what I want to do
is suppress the message box telling that it's already open reopening
will cause changes to be discarded, do you want to reopen Yes No

???

Checking for Open Workbook
 

Thanks I've been playing with Application.DisplayAlerts = Flase

But I can't seem to get the code that caused the alert part...

I still get the alert

I've tried Workbooks.Open

and have been hunting for a list of Excel Alert boxes, thinking that
would solve the problem, but I'm still straching my head on this one...