Checking for Open Workbook

  • Thread starter Thread starter sbharbour
  • Start date Start date
S

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

???
 
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.
 
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...
 
Back
Top