IsFileOpen

This page describes how to test whether a file is open.

Introduction

If your project works with files other than Excel files, you should test whether a file is already open by another process before you attempt to read it or write to it. This page describes a function named IsFileOpen that returns True if the specified file is open or returns False if the specified file is not open. The code works by simply attempting to open the file for exclusive access. If the file is open by another process, the attempt to open it will fail. If the file is not in use, the attempt to open it will succeed. Once opened, the file is immediately closed without saving.

Code For IsFileOpen

The function declaration of IsFileOpen is shown below:

Public Function IsFileOpen(FileName As String, _
    Optional ResultOnBadFile As Variant) As Variant

The FileName parameter names the file to be tested. The ResultOnBadFile parameter, if present, specifies what value to return if FileName does not exist or is a syntactically invalid file name. If present, this value will be returned. If this parameter is omitted and FileName does not exist or is invalid, the result is False.

The complete module code is shown below. You can download the bas module or you can copy the code from this page.

This page last updated: 3-May-2008

Option Explicit
Option Compare Text
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' modIsFileOpen
' By Chip Pearson, www.cpearson.com , chip@cpearson.com
' www.cpearson.com/Excel/IsFileOpen.aspx
' This module contains the IsFileOpen procedure whict tests whether
' a file is open.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Public Function IsFileOpen(FileName As String, _
    Optional ResultOnBadFile As Variant) As Variant
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' IsFileOpen
' This function determines whether a the file named by FileName is
' open by another process. The fuction returns True if the file is open
' or False if the file is not open. If the file named by FileName does
' not exist or if FileName is not a valid file name, the result returned
' if equal to the value of ResultOnBadFile if that parameter is provided.xd
' If ResultOnBadFile is not passed in, and FileName does not exist or
' is an invalid file name, the result is False.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' 

Dim FileNum As Integer
Dim ErrNum As Integer
Dim V As Variant

On Error Resume Next

''''''''''''''''''''''''''''''''''''''''''''
' If we were passed in an empty string,
' there is no file to test so return FALSE.
'''''''''''''''''''''''''''''''''''''''''''' 
If Trim(FileName) = vbNullString Then
    If IsMissing(ResultOnBadFile) = True Then
        IsFileOpen = False
    Else
        IsFileOpen = ResultOnBadFile
    End If
    Exit Function
End If

''''''''''''''''''''''''''''''''''''''''''''
' if the file doesn't exist, it isn't open
' so get out now
'''''''''''''''''''''''''''''''''''''''''''' 
V = Dir(FileName, vbNormal)
If IsError(V) = True Then
    ' syntactically bad file name
    If IsMissing(ResultOnBadFile) = True Then
        IsFileOpen = False
    Else
        IsFileOpen = ResultOnBadFile
    End If
    Exit Function
ElseIf V = vbNullString Then
    ' file doesn't exist.
    If IsMissing(ResultOnBadFile) = True Then
        IsFileOpen = False
    Else
        IsFileOpen = ResultOnBadFile
    End If
    Exit Function
End If

FileNum = FreeFile()
'''''''''''''''''''''''''''''''''''''''
' Attempt to open the file and lock it.
''''''''''''''''''''''''''''''''''''''' 
Err.Clear
Open FileName For Input Lock Read As #FileNum
ErrNum = Err.Number
''''''''''''''''''''
' Close the file.
'''''''''''''''''''' 
Close FileNum
On Error GoTo 0

''''''''''''''''''''''''''''''''''''''
' Check to see which error occurred.
'''''''''''''''''''''''''''''''''''''' 
Select Case ErrNum
    Case 0
        ''''''''''''''''''''''''''''''''''''''''''''
        ' No error occurred.
        ' File is NOT already open by another user.
        '''''''''''''''''''''''''''''''''''''''''''' 
        IsFileOpen = False
    Case 70
        ''''''''''''''''''''''''''''''''''''''''''''
        ' Error number for "Permission Denied."
        ' File is already opened by another user.
        '''''''''''''''''''''''''''''''''''''''''''' 
        IsFileOpen = True
    Case Else
        ''''''''''''''''''''''''''''''''''''''''''''
        ' Another error occurred. Assume open.
        '''''''''''''''''''''''''''''''''''''''''''' 
        IsFileOpen = True
End Select

End Function