Check if file is open and open if closed

This code simply checks to see if a file is open,

Then if uses an IF statement to goto one of either 2 different modules, if a file exists, then you can code it to do A , or if does not , then B

Hope this helps,

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 'this is forcing it to goto case-else for this part of the model.
    
End Function
 

Sub Exists()

Dim h As String

Dim spath As String
    
    h = Environ("username") 'this puts the network user ID into the value of H.
     
     '// Put your own path here obviously, the reference to environ also can be removed ofc.
    spath = "C:\Documents and Settings\" & h & "\Desktop\Data Set.xls" 'this is the path which is checked to see if the file exists
                                                                       'please note the network ID reference on the file path.
    If FileOrDirExists(spath) Then
        Call textopen '// This is if the file exists... can add anything here
    Else
        Call CreateData '\\this is if the file does not exist ... can add anything here also
    End If
    
End Sub