Ever needed to determine if an Excel Workbook was already open, perhaps a Word document?

The 2 functions below can help you determine the state of a file.

A File’s Current State

The following returns a plain English description of the state:

• File is not open
• File does not exist
• Permission denied – File is already open
• Path not found
• Unknown State

'---------------------------------------------------------------------------------------
' Procedure : File_GetState
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Determine the current state of a given file
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: None required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sFile     - Full path and filename with extension to determine the state of
'
' Usage:
' ~~~~~~
' ? File_GetState("C:\Databases\testme.xlsm")
'   Returns => File is not open
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2009-10-09             Initial Release
' 2         2023-03-02             Renamed Function, Updated Header
'---------------------------------------------------------------------------------------
Function File_GetState(sFile As String) As String
    On Error GoTo Error_Handler
    Dim iFileNo         As Integer
    
    File_GetState = "File is not open"

    iFileNo = FreeFile
    Open sFile For Input Lock Read As iFileNo
    Close iFileNo

Error_Handler_Exit:
    On Error Resume Next
    Exit Function

Error_Handler:
    Select Case Err.Number
        Case 53
            File_GetState = "File does not exist"
        Case 70
            File_GetState = "Permission denied - File is already open"
        Case 76
            File_GetState = "Path not found"
        Case Else
            File_GetState = "Unknown State"
    End Select
    GoTo Error_Handler_Exit
End Function

Is The File Currently In Use/Open?

The following function returns a boolean True/False whether the file is currently in use/open.

'---------------------------------------------------------------------------------------
' Procedure : File_IsOpen
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Determine if a file is currently open, or not
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: None required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sFile     - Full path and filename with extension to determine the state of
'
' Usage:
' ~~~~~~
' ? File_IsOpen("C:\Databases\Book1.xlsx")
'   Returns => True  - File is currenly open
'              False - File is NOT current open
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2009-10-09             Initial Release
' 2         2023-03-02             Renamed Function, Updated Header
'---------------------------------------------------------------------------------------
Function File_IsOpen(sFile As String) As Boolean
    On Error GoTo Error_Handler
    Dim iFileNo         As Integer

    iFileNo = FreeFile
    Open sFile For Input Lock Read As iFileNo
    Close iFileNo

Error_Handler_Exit:
    On Error Resume Next
    Exit Function

Error_Handler:
    Select Case Err.Number
        Case 70
            File_IsOpen = True
    End Select
    GoTo Error_Handler_Exit
End Function