Checking for Open Workbook

[sbharbour]

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.

 

[Tom Ogilvy]

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

 

[sbharbour]

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

 

[Tom Ogilvy]

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.

 

[sbharbour]

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

???

 

[Tom Ogilvy]

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.

 

[sbharbour]

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...