- Trending Categories
- Data Structure
- Operating System
- MS Excel
- C Programming
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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.
In our example, we have an Excel file opened, as shown in following image.
Press Alt+F11 key simultaneously to open Microsoft Visual Basic for Applications window. And then click Insert > Module.
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).
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".
Kickstart Your Career
Get certified by completing the courseGet Started