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
– JoaMika, Commented Feb 25, 2022 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
– JoaMika, Commented Feb 25, 2022 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
– BigBen, Commented Feb 25, 2022 at 17:17

VBasic2008 · Accepted Answer · 2022-02-25 18:00:25Z

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

Collectives™ on Stack Overflow

Excel VBA - Check if a file is open

1 Answer 1

Is File Available?

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Is File Available?

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related