Check if file is open and open if closed

[Jockster]

Hi,
I have code which will check if the file "C of S.xls" is open. Ideally, if it is open, the sub will exit but if it it closed, the sub will open "C of S.xls".
When the file is already open, everything works fine. However, when the file is closed, I get the "subscript out of range, Run time error 9" debug error at the point in the routine where it looks for the "C of S.xls" file.
How can I avoid this happening?
I have attached the file.
Thanks.

Jockster

[Andy Pope]

You can test directly against the functions returned value, no need for extra variables.

Sub Wb_Open() 'Opens R of C spreadsheet

    Dim Wb As Workbook
    Dim strFileName As String, strFullFileName As String
    
    strFileName = "R of C.xls"
    strFullFileName = "S:\Admin\Spring\Summer\2010\Automation\R of C.xls"

    If IsWorkbookOpen("R of C.xls") Then '<< Function in Module 13
        Set Wb = Workbooks(strFileName)
    Else
        Set Wb = Workbooks.Open(strFullFileName)
    End If
    
End Sub

Is it just a typo you say "C of S.xls" when the code is checking for "R of C.xls" ?

[Jockster]

Yes indeed - a typo.
Function code still gets stuck when looking for "R of C.xls" when it is not open. When it is open, the Function code doesn't detect this and tries to open "R of C.xls" again. Very frustrating.

[elmarko123]

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

[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'?

[elmarko123]

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