Excel VBA - Check if a file is open

Question

I am using this function to check if a file is open. However, it errors out with error no. 53 if the file specified as input doesn't exist. I need to add a condition, to skip the file, if it doesn't exist on the hard drive.

Public Function IsFileOpen(FileName As String)
Dim iFilenum As Long
Dim iErr As Long
On Error Resume Next
iFilenum = FreeFile()
Open FileName For Input Lock Read As #iFilenum
Close iFilenum
iErr = Err
On Error GoTo 0

Select Case iErr
Case 0:    IsFileOpen = False
Case 70:   IsFileOpen = True
Case Else: Error iErr
End Select
End Function

I basically added Case 53: IsFileOpen = False - and it works, however I am not sure if that's the right approach in this instance — JoaMika, Feb 25 '22 at 17:06
I have seen this duplicate question - but I find this code to be more clean - hence we should keep it live for others — JoaMika, Feb 25 '22 at 17:07
Side note, but it might be better to explicitly return a `Boolean`: `Public Function IsFileOpen(ByRef FileName As String) As Boolean`. — BigBen, Feb 25 '22 at 17:17

score 1 · Accepted Answer · answered Feb 25 '22 at 18:00

Is File Available?

Option Explicit

Function IsFileAvailable( _
    ByVal FilePath As String) _
As Boolean
    'If Len(Dir(FilePath)) = 0 Then Exit Function
    Dim FileNum As Long: FileNum = FreeFile()
    Dim ErrNum As Long
    On Error Resume Next
        Open FilePath For Input Lock Read As #FileNum
        Close FileNum
        ErrNum = Err.Number
    On Error GoTo 0
    IsFileAvailable = ErrNum = 0
End Function

Function FileExists( _
    ByVal FilePath As String) _
As Boolean
    FileExists = Len(Dir(FilePath)) > 0
End Function

Function IsFileOpen( _
    ByVal FilePath As String) _
As Boolean
    Dim FileNum As Long: FileNum = FreeFile()
    Dim ErrNum As Long
    On Error Resume Next
        Open FilePath For Input Lock Read As #FileNum
        Close FileNum
        ErrNum = Err.Number
    On Error GoTo 0
    IsFileOpen = ErrNum = 70
End Function

Excel VBA - Check if a file is open

1 Answers1

Is File Available?