How to quickly check if a file (workbook) is open or closed in Excel

If you operate on a team, there will inevitably be situations in which many people attempt to use the same file. It is possible that when your team member is reviewing certain information in a workbook at the same time as you are attempting to rename the file on your computer.

If you try to do this with a macro, you will most likely receive a run time error. This is because when you run files manually, it typically produces an acceptable warning message; but, if you try to do this with a macro, you will most likely receive an error. There is a strong probability that other users who will be utilizing your macro will be clueless about what the significance of this error is.

To avoid this situation, we will show in this tutorial how you can use a VBA code to see if the file is already open by any other user.

Check if a Workbook is Open or Not Using VBA

Excel requires you to have numerous workbooks open at once so that you can work on them simultaneously; but, when you have dozens of workbooks open at once, it can be difficult to keep track of which workbooks are open and which are closed.

Follow the below given steps to add VBA code to check whether the workbook is opened or not.

Step 1

In our example, we have an Excel file opened, as shown in following image.

Step 2

Press Alt+F11 key simultaneously to open Microsoft Visual Basic for Applications window. And then click Insert > Module.

Step 3

Then add the following VBA code on the blank module.

Function IsFileOpen(Name As String) As Boolean
    Dim xWb As Workbook
    On Error Resume Next
    Set xWb = Application.Workbooks.Item(Name)
    IsFileOpen = (Not xWb Is Nothing)
End Function

Then call the above function in the given code by passing the file name as argument to the above function.

Sub Sample()
    Dim xRet As Boolean
    xRet = IsFileOpen("Test.xlsx")
    If xRet Then
        MsgBox "The file is open", vbInformation, "VBOutput"
    Else
        MsgBox "The file is not open", vbInformation, "VBOutput"
    End If
End Sub

In the above code, Test.xlsx is the name of the workbook that we are going to check (whether it is open or closed).

Step 4

Then run the code by pressing F5 or go to Run > Run Sub/UserForm.

Running the above code will produce the output as given in the following image.

If we close the file and then run the code, it will produce the output as "The file is not opened".