Excel VBA - Check if a file is open-CodePudding

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

CodePudding user response：

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