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.
Dive Deep into Macros! Make Excel do things you thought were impossible, discover techniques you won't find anywhere else, and create powerful automated reports. Bill Jelen and Tracy Syrstad help you instantly visualize information to make it actionable. You’ll find step-by-step instructions, real-world case studies, and 50 workbooks packed with examples and solutions. Check out Microsoft Excel 2019 VBA and Macros today!
There are multiple ways that Excel can create a workbook, and the formatting in each creation method can be different. In ...
Discover MoreDo you often need to know the difference between two values in your worksheet? This tip shares a quick little macro that ...
Discover MoreSometimes it may be helpful for a macro to know exactly where it is being executed. This tip provides a way that you can ...
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