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.
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 Else 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.
- 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.
- In Step 2, we are telling 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.
- 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.
- 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 FileIsOpenTest to True. If an error occurred, that means the workbook is not open. In that case, we set the FileIsOpenTest to False.
Tips: 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.
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", _ MultiSelect:=False) '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" Else Workbooks.Open FileName:=FName End If End If End Sub
If the workbook is open, it will display a message box: The chosen file is already open.
How to Use This Macro
To implement this macro, you need to copy and paste it into the standard module:
- Activate the Visual Basic Editor by pressing
- In the Project window, find your project/workbook name and Right-click the project/workbook name in the Project window.
- Choose Insert ➜ Module.
- Type or paste the code in the newly created module, modifying the GetOpenFilename method parameters(if necessary).
- Optionally, you can assign the macro to a button (see “How to Assign a Macro to a Button Form Controls or a Sharp”).