Check if workbook is already open

[johnske]

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]

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]

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]

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]

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?)