Checking to see if a Workbook is Open

T

Todd Huttenstine

I have some code that I do NOT want to execute if Workbook "Stats
Manager.xls" is open. What is the code to check to see if "Stats
Manager.xls" is open?


Thank you

Todd Huttenstine
 
B

Bob Phillips

Todd,

Here's a simple little function that checks for a workbook being open

Function IsWBOpen(Name As String)
Dim oWB As Workbook

On Error Resume Next
Set oWB = Workbooks(Name)
On Error GoTo 0
IsWBOpen = Not (oWB Is Nothing)

End Function


Test with

If IsWBOpen("Stats Manager.xls") Then
....

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
C

Chip Pearson

Todd,

You can use the following function proc.

Function IsWorkbookOpen(WBName As String) As Boolean
On Error Resume Next
IsWorkbookOpen = CBool(Len(Workbooks(WBName).Name))
End Function

Then, you can call it from code with a statement like

If IsWorkbookOpen(WBName:="Stats Manager.xls") = True Then
' do you thing
End If


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
R

Ron de Bruin

Hi Todd

Try this example that is using 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
 
Top