VBA Express : Excel - Function to check if workbook is open or not.

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:

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.