Skip to main content

Excel Macro: Determine If a Excel Workbook is Already Open

The last macro automatically opened a specific workbook chosen by the user's selection. As we consider automatically opening workbooks, you must think about what may happen if you attempt to open a workbook that is already open. In the non-VBA world, Excel attempts to open the workbook again, with a warning that any unsaved changes will be lost. In VBA, it's a great idea to protect against such an occurrence by checking if a given file is already open before trying to open it again. The function below can be used to determine if a excel workbook is already open or not.

Determine If a Excel Workbook is Already Open

'------------------ Modules ------------------
Function WorkbookOpenTest(TargetWorkbook As String) As Boolean
'Step 1:  Declare variables
    Dim TestWorkbook As Workbook

'Step 2: Tell Excel to Resume on Error
    On Error Resume Next

'Step 3: Try to assign the target workbook to TestBook
    Set TestWorkbook = Workbooks(TargetWorkbook)

'Step 4: If no error occurred then Workbook is already open
    If Err.Number = 0 Then
        WorkbookOpenTest = True
        WorkbookOpenTest = False
    End If
End Function

How This Macro Works

The first thing to notice about this macro WorkbookOpenTest is a function, not a sub procedure. As you will see, making this macro a function enables us to pass any filename to it to test whether that file is already open.

The gist of this code is simple. We are testing a given filename to see if it can be assigned to an object variable. Only opened workbooks can be assigned to an object variable. When we try to assign a closed workbook to the variable, an error occurs.

So if the given workbook can be assigned, the workbook is open; if an error occurs, the workbook is closed.

  1. The first thing the macro does is to declare a string variable that will hold the filename that the user chooses. TestWorkbook is the name of our string variable.
  2. In Step 2, we use the On Error statement to tell Excel that there may be an error running this code. In the event of an error, resume the code. Without this line, the code would simply stop when an error occurs. Again, we are testing a given filename to see if it can be assigned to an object variable. So if the given workbook can be assigned, it’s open; if an error occurs, it’s closed. We need to have the code continue if an error occurs.
  3. In Step 3, we are attempting to assign the given workbook to the TestWorkbook object variable. The workbook we are trying to assign is itself a string variable called TargetWorkbook. TargetWorkbook is passed to the function in the function declarations (see the first line of the code). This structure eliminates the need to hard-code a workbook name, allowing us to pass it as a variable instead.
  4. In Step 4, we simply check to see if an error occurred. If an error did not occur, the workbook is open, so we set the WorkbookOpenTest to True. If an error occurred, that means the workbook is not open. In that case, we set the WorkbookOpenTest to False.

Again, this is a function that can be used to evaluate any file you pass to it, via its TargetWorkbook argument. That is the beauty of putting this macro into a function. The following macro demonstrates how to implement this function. Here, we are using the same macro you saw in the previous macro, “Opening a Specific Workbook Chosen by the user” but this time, we are calling the new WorkbookOpenTest function to make sure the user cannot open an already open file.

'------------------ Modules ------------------
Sub FileOpenTest()
'Step 1: Define a string variable.
    Dim FName As Variant
    Dim FNFileOnly As String

'Step 2: GetOpenFilename Method activates dialog box.
    FName = Application.GetOpenFilename( _
                        FileFilter:="Excel Workbooks,*.xl*", _
                        Title:="Choose a Workbook to Open", _

'Step 3: Open the chosen file if not already opened.
    If FName <> False Then
        FNFileOnly = StrReverse(Left(StrReverse(FName), _
                     InStr(StrReverse(FName), "\") - 1))
        If WorkbookOpenTest(FNFileOnly) = True Then
            MsgBox "The chosen file is already open"
            Workbooks.Open Filename:=FName
        End If
    End If
End Sub

If the workbook is open, it will display a message: The chosen file is already open.

Most VBA code should be placed in Standard Modules unless specified.

If you see a comment '------------------ Modules------------------ in the code header that means put the code in a Standard Module. For more information, learn this course: Where should I put the Excel VBA code?

The following steps teach you how to put VBA code into a Standard Module:

  1. Activate the Visual Basic Editor by pressing ALT + F11.
  2. Right-click the project/workbook name in the Project Window.
  3. Choose Insert -> Module.
  4. Type or paste the code in the newly created module. You will probably need to change the sheet name, the range address, and the save location.
  5. Click Run button on the Visual Basic Editor toolbar.
  6. For more information, learn this course: Programming with Excel VBA

Leave a comment

Your email address will not be published. Required fields are marked *

Format your code: <pre><code class="language-vba">place your code here</code></pre>