Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Checking if a Workbook is Already Open.
Written by Allen Wyatt (last updated February 22, 2025)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365
Macros are often used to slice, dice, and otherwise process information contained in workbooks. This presumes, of course, that the workbook that contains the information is actually open. If it is not, then your macro will obviously need to include code to actually open the needed workbook.
Opening a workbook can really slow down a macro; it takes time to access the disk and load the file. Thus, if your macro can check to see if a workbook is open before going through the hassle of actually trying to open it, you could speed up your macros greatly if the workbook is found to already be open.
One very flexible way to approach the task of checking whether a workbook is open is to use a function that does the checking, and then simply returns a TRUE or FALSE value based on whether the workbook is open. The following short macro performs this succinct task:
Please Note:
This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here:
Function AlreadyOpen(sFname As String) As Boolean
Dim wkb As Workbook
On Error Resume Next
Set wkb = Workbooks(sFname)
AlreadyOpen = Not wkb Is Nothing
Set wkb = Nothing
End Function
To use the function, just pass it the name of the workbook you want to check, in the following manner:
sFilename = "MyFileName.xls"
sPath = "C:\MyFolder\MySubFolder\"
If AlreadyOpen(sFilename) Then
'Do not have to open
Else
Workbooks.Open sPath & sFilename
End If
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (10985) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365. You can find a version of this tip for the older menu interface of Excel here: Checking if a Workbook is Already Open.
Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!
Macros can be used to change the formatting of your worksheet, if desired. One change you might want to make is to the ...
Discover MoreGot some pesky blank rows in your data that you want to get rid of? This tip provides a wide variety of methods you can ...
Discover MorePerhaps the most common way of communicating with programs is through the use of dialog boxes. We expect dialog boxes to ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2025-02-24 02:21:47
Simon
Thanks Allen and J Woolley. Very helpful.
2025-02-23 11:37:39
J. Woolley
Re. the Tip's VBA code:
1. Since Function AlreadyOpen is not declared Private, it can be used in a cell formula (assuming the module is not Private).
2. If the Workbooks.Open method successfully opens a workbook, that workbook becomes the active workbook; therefore, you might want to keep track of the current ActiveWorkbook by assigning it to a variable of type Workbook before using Workbooks.Open.
3. If Workbooks.Open is used to open a workbook that is already open, that workbook is quickly activated; therefore, there is no need to use the AlreadyOpen function unless your computer is very old and slow. (This assumes a modern version of Excel that opens all workbooks in a single instance of Application; versions prior to Excel 2013 might behave differently.)
2025-02-22 12:41:07
J. Woolley
@Simon
I had already prepared the following answer, but Allen beat me to it.
The Application.Workbooks property is a collection of all open workbooks. When the Workbooks property has no object qualifier, Application is assumed.
Collection items are returned by base-1 index (number) or by key (text). For the Workbooks collection, all open Workbook objects except add-in workbooks can be returned by index or key; add-ins can only be returned by key.
Variable wkb is declared a Workbook object. When a procedure starts, all objects are initialized to Nothing.
On Error Resume Next means that execution will continue at the next statement following any error; the statement with the error will not complete.
If sFname is not the name of an open workbook in the Workbooks collection, Workbooks(sFname) will cause an error and wkb will not be Set. In this case, wkb remains Nothing and AlreadyOpen returns False.
If sFname is the name of an open workbook in the Workbooks collection, Workbooks(sFname) will not cause an error and wkb will be Set. In this case, wkb is not Nothing and AlreadyOpen returns True.
The last statement releases wkb from memory; this is optional. Without the last statement, memory used by the procedure will be released anyway (but early versions of VBA might have been less efficient). The last statement has no effect on the Workbooks collection.
Some would add On Error GoTo 0 as a final statement to clear the Err object; this is optional. Without that final statement, the Err object is not cleared and is returned to the procedure that called AlreadyOpen. In this case, Err.Number will be 9 if Workbooks(sFname) caused an error.
2025-02-22 12:32:28
Allen
Simon,
If a workbook is open in Excel, it will be a part of the Workbooks collection. The AlreadyOpen function tries to set the wkb variable equal to the desired workbook in the Workbooks collection. If it is unsuccessful (meaning, the workbook is not in the collection and therefore not open), then wkb will be equal to Nothing. So, AlreadyOpen is set to True if wkb is Not Nothing, and set to False if wkb is Nothing. It is this True/False value that is returned by the function.
Hope that helps.
-Allen
2025-02-22 05:31:24
Simon
Could Allen or someone please explain how the user defined function AlreadyOpen works. Many thanks.
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments