Identify open Workbooks

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am looking for the VBA to identify if a workbook is already open and, if
not, to open it, otherwise to open the workbook window.

I have:-
i) Worksbooks.Open("Filename.xls")

and
ii) Windows("Filename.xls).Activate

but I require somethiing to stop the 'overwrite' message popping up after
running i), if already open, and then running ii)

Hope this makes sense

Any help appreciated.
 
Hi SyZyGy

One way

You can test it with a function

Function bIsBookOpen(ByRef szBookName As String) As Boolean
' Rob Bovey
On Error Resume Next
bIsBookOpen = Not (Application.Workbooks(szBookName) Is Nothing)
End Function

Sub File_Open_test()
If bIsBookOpen("Book11.xls") Then
MsgBox "the File is open!"
Else
MsgBox "the File is not open!"
End If
End Sub
 
Ron

Thanks for the response

I do not want the user to have to test if the workbook is already open. The
default microsoft 'overwrite' message prompt is effectively doing this.

My requirement is to hide this test from the user and just open the workbook
window

regards - SyZyGy
 
I am looking for the VBA to identify if a workbook is already open and, if
not, to open it, otherwise to open the workbook window.
Click to expand...

Sub File_Open_test()
If bIsBookOpen("Book11.xls") Then
'you open code
Else
'your activate code
End If
End Sub

The code is doing what you want if you add the open and activate code line
 
Back
Top