Checking to see if a Workbook is Open

  • Thread starter Thread starter Todd Huttenstine
  • Start date Start date
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
 
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)
 
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
 
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
 
Back
Top