|
|
|
|
|
|
Excel
|
Function to check if workbook is open or not.
|
|
Ease of Use
|
Easy
|
Version tested with
|
2000, 2002, 2003
|
Submitted by:
|
Zack Barresse
|
Description:
|
This function will return either True or False depending if the specified workbook (name) is open or not. Can be used in a VBA routine or as a standard worksheet function.
|
Discussion:
|
When running code, sometimes you have the need to test if a workbook is already opened or not. Whether you need to use it for error handling or in an add-in where you won't know if a user has opened a workbook or not, this can be used in many situations. It goes very well in an If/Then clause for creating 'error free' variable setting (example below). This will act as a fail-safe of sorts when working with multiple workbooks, to test if the user has the workbook open or not.
Used as a User Defined Function, this ability will be obtainable in the worksheet (functions) setting as well.
This procedure will also return any hidden instances (i.e. Personal.xls). Also, if by chance, another instance of Excel has been opened (different from just another workbook) it will not search through these other instance(s) to check if specified book is open.
|
Code:
|
instructions for use
|
Option Explicit
Option Compare Text
Function IsWbOpen(wbName As String) As Boolean
Dim i As Long
For i = Workbooks.Count To 1 Step -1
If Workbooks(i).Name = wbName Then Exit For
Next
If i <> 0 Then IsWbOpen = True
End Function
Sub Test()
Dim wb As Workbook, strName As String, strPath As String
strName = "Target Book.xls"
strPath = "C:\Documents and Settings\Rob\Desktop\"
If IsWbOpen(strName) Then
Set wb = Workbooks(strName)
wb.Activate
Else
Set wb = Workbooks.Open(strPath & strName)
End If
End Sub
|
How to use:
|
- Copy above code.
- In Excel press Alt + F11 to enter the Visual Basic Editor (VBE).
- Press Ctrl + R to show the Project Explorer.
- Right-click desired file on left (in bold).
- Choose Insert -> Module.
- Paste code into the right pane.
- Press Alt + Q to close the VBE.
- Save workbook before any other changes.
- Call Function as either 1) a worksheet function, or 2) within a VBA routine.
|
Test the code:
|
- Two methods (a and b); from an existing workbook, save first.
- a) Select an empty cell.
- a) Type =IsWbOpen("Book1.xls")
- a) A FALSE return will be given if there is no open workbook with this name.
- b) From an open module in the VBE, run:
- b) If IsWbOpen("Book1.xls") Then MsgBox "Book1.xls is open."
- A test procedure is included in the zipped example workbook.
|
Sample File:
|
IsWbOpenEx.zip 7.66KB
|
Approved by mdmackillop
|
This entry has been viewed 292 times.
|
|