PDA

View Full Version : [SOLVED] Check if workbook is already open



johnske
11-26-2004, 10:03 PM
Hi all,

This should be really basic, but...my memory's failing me

I have code to open and activate a workbook. The inherent assumption is that the book is closed and it works fine when it is closed, but of course it will error out if the book is already open so there should be an If..Then..Else command there. But how do test if the book is open? (I'm sure I've seen/done this somewhere before...)


If '<<the workbook is open>> Then
Workbooks(ArchersName).Activate
Else
Application.Workbooks.Open("C:\Windows\Desktop\" & _
"NewKeeper\DBs\" & ArchersName & ".xls") _
.Activate
End If

Ken Puls
11-26-2004, 10:23 PM
Hello,

I think that this is a John Walkenbach creation, but could be corrected... (Have it stored in a code library here.)


Private Function WorkbookIsOpen(WBname) As Boolean
Dim x As Workbook
On Error Resume Next
Set x = Workbooks(WBname)
If Err = 0 Then WorkbookIsOpen = True _
Else WorkbookIsOpen = False
End Function

HTH,

johnske
11-26-2004, 10:37 PM
Thanx Ken! :)

Haven't tried it yet 'cos I did a search and got this one just before your reply arrived (by Joseph Ruben, it works also)


If Not WorkbookOpen(ArchersName) Then
Application.Workbooks.Open("C:\Windows\Desktop\" & _
"NewKeeper\DBs\" & ArchersName & ".xls") _
.Activate
Else
Workbooks(ArchersName).Activate
End If

'//the function for this is:



Function WorkbookOpen(WorkBookName As String) As Boolean
' returns TRUE if the workbook is open
WorkbookOpen = False
On Error GoTo WorkBookNotOpen
If Len(Application.Workbooks(WorkBookName).Name) > 0 Then
WorkbookOpen = True
Exit Function
End If
WorkBookNotOpen:
End Function

Jacob Hilderbrand
11-26-2004, 11:11 PM
We also have a kb entry on this:

http://www.vbaexpress.com/kb/getarticle.php?kb_id=207

But basically just try to activate it to see if you get an error or not. If there is an error then the workbook is not open.

johnske
11-27-2004, 12:48 AM
Thanx Jacob, I KNEW I'd seen this topic before! - but from memery I thort it was in a thread and went searching for it thru pages & pages of threads before posting the question - never thort of the KB (it really shooda been my first thort eh?) :vv