Abort Macro IF SAVE AS location is unavailable?

[bcurrey]

I have scheduled queries running that running about every 15 minutes to extract data from our system. I then have macros attached to the reports that run and format the data into a usable format. Once finished, the files are saved to a network drive where users can access them.

I've run into some issues where a user will go to the file and open it rather than copy it. If it's open when the new query and macro execute a error is created and several people get emailed of the issue.

To get around this I found some code that (I think) will check to see if the end file is open and then abort the macro if it is. I don't think the code is working though, because I'm still getting some errors. Can someone let me know if this code is what I need or if I have something wrong within it? Thanks!

[B]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[/B]


Sub Auto_Open()

Workbooks.Open FileName:="D:\pbs\temp\ds88\q1.xls"
       
   
[B]If Not IsFileOpen("D:\data\fci_queries\Real Time Stats\S2k\files\fskwip.xls") Then
[/B]                  

'THIS IS WHERE I HAVE MY REGULAR MACRO

 
    Application.DisplayAlerts = False
  
  ActiveWorkbook.SaveAs FileName:="D:\data\fci_queries\Real Time Stats\S2k\files\fskwip.xls", FileFormat:=xlNormal, _
        Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
        CreateBackup:=False
    Application.Quit
    
    
    End If
End Sub

 

[dmt32]

As a thought, in your Function you have FileName as a variable. This is a reserved name in VBA & you should avoid using them in the way you have. To get around this ou can prefix with another character.

I have made some changes to your function which may do what you want but no promises. Its trial & error time.

Function IsFileOpen(sFileName As String)
    On Error Resume Next
    Open sFileName For Binary Access Read Lock Read As #1
    Close #1
    IsFileOpen = IIf(Err.Number <> 0, True, False)
    On Error GoTo 0
End Function

Dave

 

[bcurrey]

This worked perfect. Thanks Dave!