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 '\\ Example Sub Test() Dim wb As Workbook, strName As String, strPath As String strName = "Target Book.xls" strPath = "C:\Documents and Settings\Rob\Desktop\" '<<== CHANGE TO SUIT If IsWbOpen(strName) Then Set wb = Workbooks(strName) 'Make the workbook the Active Workbook wb.Activate Else Set wb = Workbooks.Open(strPath & strName) End If End Sub

How to use:

  1. Copy above code.
  2. In Excel press Alt + F11 to enter the Visual Basic Editor (VBE).
  3. Press Ctrl + R to show the Project Explorer.
  4. Right-click desired file on left (in bold).
  5. Choose Insert -> Module.
  6. Paste code into the right pane.
  7. Press Alt + Q to close the VBE.
  8. Save workbook before any other changes.
  9. Call Function as either 1) a worksheet function, or 2) within a VBA routine.
 

Test the code:

  1. Two methods (a and b); from an existing workbook, save first.
  2. a) Select an empty cell.
  3. a) Type =IsWbOpen("Book1.xls")
  4. a) A FALSE return will be given if there is no open workbook with this name.
  5. b) From an open module in the VBE, run:
  6. b) If IsWbOpen("Book1.xls") Then MsgBox "Book1.xls is open."
  7. A test procedure is included in the zipped example workbook.
 

Sample File:

IsWbOpenEx.zip 7.66KB 

Approved by mdmackillop


This entry has been viewed 280 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2014 VBA Express