Runtime error '1004': cannot access...

[rsford31]

I'm getting an error stating that there is a "Runtime error '1004': cannot access '***.csv'. The window that this error is displaying in has the buttons "End" "Debug" and "Help". This is the code:

Public Flag As Boolean

Sub ReRun()
 
nextRun = Now + TimeValue("00:00:03")
Application.OnTime nextRun, "ReRun"
 
Call saveworkbook
End Sub
 
Sub StartMacro()
'Run this macro to start "YourMacro"
Flag = False
 
Call ReRun
End Sub
 
Sub Auto_Close()
'Turns off the OnTime event when closing the file
Call StopMacro
End Sub
 
Sub StopMacro()
'Run this to turn off the OnTime event
On Error Resume Next
Application.OnTime nextRun, "ReRun", schedule:=False
End Sub
 
Sub saveworkbook()
 
If Flag = False Then
    Flag = True
    ActiveWorkbook.SaveAs Filename:="c:\folder\filename.csv", FileFormat:=xlCSV
    
Else
    ActiveWorkbook.Save
End If
End Sub

When the "Debug" button is clicked the error points to ActiveWorkbook.Save
The user then has to shut down the macro and then rerun it. Is there any way to have it catch the error and retry?

[Leith Ross]

Hello rsford31,

Welcome to the Forum!

I suspect the error is occurring because the workbook has not been saved before. Has it?

[rsford31]

Thanks!

I believe it has been saved. There is a csv file in that path on the server. What's happening is there is live data being fed into the csv file. The macro's job is to save the file every 3 seconds. But these errors pop up and the macro has to be restarted. The errors don't occur all the time either. They are sporadic.

[Leith Ross]

Hello rsford31,

It is a synchronization problem. Only one program at a time can have a file open. The error occurs when you try to close the file to save it and the query is still has the file open. Not really sure how to workaround that other than trapping the error and try again. Probably need to count the number of retries before aborting.