FileIsOpen(): Check If Another User or Process Has a File Locked

Trying to modify or delete a file that's already in use can lead to some misleading error messages.

An error message like "Access denied" suggests that there is a problem with file permissions.  And sometimes that is the case.  But sometimes, the operating system is denying you access to the file because another process is already using it and has placed a lock on it.

The FileIsOpen() function is a simple way to check if a file is already in use before trying to modify or delete it.

Misleading and Cryptic Error Messages

When working with files in VBA, some errors can be particularly cryptic, leading developers down the wrong troubleshooting path.

Here are some common yet misleading errors you might encounter when interacting with locked files, along with explanations that clarify their true meaning:

• "Permission Denied" (Error 70): This error message might make you think there's a problem with your access rights, but it can also occur when you're trying to write to a file that another process has locked.
• "Path/File access error" (Error 75): This vague message could be interpreted as an issue with the file path or existence, but it's also a common error when a file is open and locked by another application.
• "The process cannot access the file because it is being used by another process": This error is slightly more descriptive, clearly indicating a file lock issue, but it can still be confusing because it doesn't specify which process is causing the lock.

These errors can be particularly frustrating because they don't always point directly to the issue at hand. As a developer, your first instinct might be to check user permissions, file paths, or server availability. However, when dealing with file operations in VBA, it's essential to consider the possibility of file locks.

A guard clause with a preemptive call to FileIsOpen() can save you some troubleshooting heartache.

The Approach

The approach we're discussing involves attempting to open the target file in a controlled manner that will not disrupt any existing process using the file.

By trying to open the file for input with a lock, we can determine if the file is already open elsewhere. If the file is open, the operation will fail, and an error will be generated.

The code is a bit simplistic, in that it assumes that any error when trying to open a file with an input lock is proof that the file is open.  In reality, there could be other explanations.  In practice, though, I've found the function to be perfectly serviceable as is.

The Function

Here is the FileIsOpen() function on its own without its required dependencies:

Function FileIsOpen(FullPath As Variant) As Boolean
    On Error Resume Next

    If IsNull(FullPath) Then Exit Function
    If Not FileExists(CStr(FullPath)) Then Exit Function

    Dim FileNum As Integer
    FileNum = FreeFile()

    Open FullPath For Input Lock Read As FileNum
    Close FileNum

    If Err <> 0 Then
        FileIsOpen = True
    End If
End Function

The Full Code

The code below includes required helper functions and concepts that I've written about in the past:

• FileExists()
• Tools > References... Safe List (New Scripting.FileSystemObject)
• Throw() (appears in the sample usage comment)
• File System Variable Naming Convention (e.g., fpTarget)
• Guard Clauses

'---------------------------------------------------------------------------------------
' Procedure : FileIsOpen
' DateTime  : 9/4/2007 11:54
' Author    : Mike Wolfe
' Source    : https://nolongerset.com/fileisopen/
' Purpose   : Returns a boolean to indicate whether a file is currently open.
' Notes     - Returns False if the value of FullPath is Null.
'           - Returns False if the file does not exist.
'           - Returns False if the file exists AND can accept a file lock (as this 
'               indicates that no other process currently has the file locked).
' Usage     : Use in a guard clause before attempting to modify an existing file:
'   Sub ModifyFile(fpTarget As String)
'       If Not FileExists(fpTarget) Then Throw "File {0} not found", fpTarget
'       If FileIsOpen(fpTarget) Then Exit Sub
'       'Safe to modify the target file; do that here
'   End Sub
'---------------------------------------------------------------------------------------
Function FileIsOpen(FullPath As Variant) As Boolean
    On Error Resume Next

    If IsNull(FullPath) Then Exit Function
    If Not FileExists(CStr(FullPath)) Then Exit Function

    Dim FileNum As Integer
    FileNum = FreeFile()

    Open FullPath For Input Lock Read As FileNum
    Close FileNum

    If Err <> 0 Then
        FileIsOpen = True
    End If
End Function

'Source: https://nolongerset.com/the-subtle-dangers-of-dir/
Function FileExists(FullPathOfFile As String)
    Dim HasWildcards As Boolean
    HasWildcards = (InStr(FullPathOfFile, "*") > 0) Or (InStr(FullPathOfFile, "?") > 0)
    
    If HasWildcards Then
        FileExists = (Len(Dir(FullPathOfFile)) > 0)
    Else
        Dim oFSO As New Scripting.FileSystemObject
        FileExists = oFSO.FileExists(FullPathOfFile)
    End If
End Function

The code can be copied and pasted into a blank standard module for a fully-working solution that can be easily integrated into your projects.

This simple check can prevent the application from attempting to write to a file that cannot be modified, avoiding potential errors and improving the user experience.