Check if file is already open using excel VBA
Dear LEM Readers,
While doing programming with VBA many a times it happens to open an existing file. Before opening that file, it is always a good idea to check if that file is already open or not. Therefore I have written a function (IsFileOpen()) which takes the complete path of that file (with file name) as an Input parameter and returns a Boolean status TRUE or FALSE as shown below:
Technique used : In this function first I will open the file as given in the input parameter and capture the error number. For every error occurred there is always a unique number based on which we can decide, the reason of that error. If we are trying to open and already opened file then the Number of the error occurred is 70. Based on this technique I have tried to find if that file is already opened.
Function IsFileOpen(fileFullName As String) Dim FileNumber As Integer Dim errorNum As Integer On Error Resume Next FileNumber = FreeFile() ' Assign a free file number. ' Attempt to open the file and lock it. Open fileFullName For Input Lock Read As #FileNumber Close FileNumber ' Close the file. errorNum = Err ' Assign the Error Number which occured On Error GoTo 0 ' Turn error checking on. ' Now Check and see which error occurred and based ' on that you can decide whether file is already ' open Select Case errorNum ' No error occurred so ErroNum is Zero (0) ' File is NOT already open by another user. Case 0 IsFileOpen = False ' Error number for "Permission Denied." is 70 ' File is already opened by another user. Case 70 IsFileOpen = True ' For any other Error occurred Case Else Error errorNum End Select End Function
How to use the above function?
Copy and Paste the above code in any of the module in your excel VBA code window. Now in that workbook you can call the function IsFileOpen() from anywhere in the VBA code. Output of this function is always Boolean type either TRUE or False.
This function can be used as a Cell Formula as well. In this workbook you can use this function as an excel formula which will take the same input and return TRUE of file is open and FALSE if not.
1. Calling in VBA code
Sub Test() ' While passing the FileName you need ' to pass complete path of the file ' including the file name with extension If IsFileOpen("C:/..../File1.xls") = True Then MsgBox ("File is already opened") Else MsgBox ("File is not opened yet") End If End Sub
2. Using as an Excel Formula (as a UDF – User Defined Function)
You can use this as a formula as well. Refer the below picture:
My name is Vishwamitra Mishra. Friends Call me Vishwa. This blog is authored by me. I am an Excel Geek. Well, this blog talks a lot about my passion in Excels & Macros so I’ll not talk about it :) I am very much passionate about traveling & quite recently discovered that I am a good photographer too..:P
AND GET A FREE!
E-BOOK FOR EXCEL VBA BEGINNERS
DON'T MISS ANY NEW ARTICLE !
Get your FREE! E-Book & Have Every New Article Delivered Straight To Your Email-Box