MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Check for Open Workbooks


Posted by Ken Brading on January 06, 2000 3:21 PM

Is there a VBA Excel command that checks to see if a particular workbook has already been opened?
If not, what do you suggest?
Ken Brading


Posted by Ivan Moala on January 06, 2000 4:33 PM

This function from John Walkenback should do it;

Private Function WorkbookIsOpen(wbname) As Boolean
' Returns TRUE if the workbook is open
Dim x As Workbook
On Error Resume Next
Set x = Workbooks(wbname)
If Err = 0 Then WorkbookIsOpen = True _
Else WorkbookIsOpen = False
End Function

http://www.j-walk.com/ss

Have a look @ his site for some GREAT stuff.


Ivan

Posted by Ken Brading on January 07, 2000 3:33 PM

Thank you for your response. I have been
experimenting with the code you recommended and it
doesn't toggle from False to True when the
WorkbookIsOpen function is programmed into a cell.
My aplication requires the used of manual
recalculate. It will however toggle when the cell
is selected and the Insert>Function box is used.
What am I doing wrong?
Ken Brading

Posted by Ken Brading on January 07, 2000 3:36 PM

Thank you for your response. I have been
experimenting with the code you recommended and it
doesn't toggle from False to True when the
WorkbookIsOpen function reference is programmed
into a cell. My aplication requires the used of
manual recalculate. It will however toggle when
the cell is selected and the Insert>Function box
is used. What am I doing wrong?
Ken Brading

Posted by Ivan Moala on January 08, 2000 1:46 AM

Ken try;Function WkbkIsOpen(wbname) As Boolean
' Returns TRUE if the workbook is open
Application.Volatile
Dim x As Workbook
On Error Resume Next
Set x = Workbooks(wbname)
If Err.Number = 0 Then WkbkIsOpen = True _
Else WkbkIsOpen = False
End Function

Ivan

Posted by Ken Brading on January 10, 2000 10:05 AM


Ivan,
Thank you for your assistance.
The code you recommended worked great!!

Ken Ivan