MrExcel Publishing
Your One Stop for Excel Tips & Solutions

How do I check if a worksheet file is open in VB?


Posted by Jim Denham-Vaughan on October 23, 2000 12:12 AM

I want to open a worksheet from a Macro. However, if I use Worksheets.Open then I get an error if it has already been opened. How can I use an if...then to check first to see if it is open to avoid this error?

OK, I know this one is probably simple, but it's stuck me!


Posted by Ivan Moala on October 23, 2000 12:39 AM

Use an error handler to check if there were
any errors when opening eg. the following
function will check for this.....but you can use
the basics of the error handler...

Function WorkBookIsOpen(wbname) As Boolean
'Returns TRUE if the workbook is open
Application.EnableEvents = False
On Error Resume Next
Workbooks.Open FileName:=wbname
If Err.Number = 0 Then
WorkBookIsOpen = True
Else
Workbooks(wbname).Close
WorkBookIsOpen = False
End If
Application.EnableEvents = True
End Function


Ivan