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 19, 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.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
Macros are often used to process information in a workbook. If your macro makes changes in what is selected in the ...
Discover MoreMacros can be used to change the formatting of your worksheet, if desired. One change you might want to make is to the ...
Discover MoreGot a workbook cluttered with all sorts of macros? Delete them and you'll make your workbook easier to manage.
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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