Identify open Workbooks
 

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.

Identify open Workbooks
 

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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"SyZyGy" wrote in message ...
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.

Identify open Workbooks
 

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

"Ron de Bruin" wrote:

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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"SyZyGy" wrote in message ...
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.

Identify open Workbooks
 

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.

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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"SyZyGy" wrote in message ...
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

"Ron de Bruin" wrote:

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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"SyZyGy" wrote in message ...
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.

Identify open Workbooks
 

Thanks Ron

It took a bit of time for my brain to get into gear!

Regards - SyZyGy

"Ron de Bruin" wrote:

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.

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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"SyZyGy" wrote in message ...
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

"Ron de Bruin" wrote:

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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"SyZyGy" wrote in message ...
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.