Check if file is open and open if closed

Originally Posted by Jockster

Thanks elmarko,
Have tried your code but when I changed the file name to check the 'else' part of the function, I get the debug window with "Run-time error '53': File not found". So, even with the 'on error resume next' and 'on error goto 0' in place, the code still halts and displays the debug window.
Is there something in place which is cancelling out the error handling 'override'?

The function part of the code can be left as it is,

You only need to edit below the line for the path and what you want it do if it does, or does not exist

Option Explicit
 
Function FileOrDirExists(spath As String) As Boolean 'boolean is a true or false statement.

    Dim iTemp As Integer 'integer is a numeric value which also allows decimal places.
     
On Error Resume Next
    iTemp = GetAttr(spath)
      
If iTemp > 1 Then

    FileOrDirExists = True 'if there are no errors on obtaining the pathname then the file exists
        Else
    FileOrDirExists = False 'anything else then the file does not exist & can be created
   
End If

On Error GoTo 0 

End Function

------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 

Sub Exists()

Dim h As String

Dim spath As String

     
    spath = "   Put Your File Path in this gap between the speech marks    " 

                   (Example) =    "C:\Documents and Settings\User\Desktop\Data Set.xls"

    If FileOrDirExists(spath) Then
        ****What you want it do do if it does exist******
    Else
         ****What you want it do do if it does NOT exist******
    End If
    
End Sub