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?

 

[noboffinme]

Hi rsford31

I ran that piece of code & got an error on saving as.

The yellow highlighted line of code couldn't run so your error is about the 'save as' line for the csv file.

When I ran your code, the error I got was due to the csv workbook having more than one worksheet. (csv doesn't like to save with > 1 w/sheets)

This runtime error you saw was advising it can't access the workbook you want to save - the activeworkbook.

Do you have > 1 worksheets in this workbook? if so, try saving an .xls file instead or delete the extra w/sheets.

Btw, A 'Runtime' error just means the error occurred while the code was running.

The Debug, End & Help options are there to either; go to the line where the code has stopped (Debug) ignore & close (End), or Help

Hope this helps

 

[rsford31]

Hi, Thanks for the response. This workbook has three sheets. I'll get them to try and remove the two extra sheets. How did you get it to fail? I created a csv file with the same name and with all of the same sheet names and never got the error.

 

[rsford31]

Hi, I got the client to delete Sheet2 and Sheet 3 so only the one sheet remained and they are still receiving the error. Any other ideas? Thanks!

 

[noboffinme]

OK, Ive got a couple of questions;

I'm trying to understand what you're macro is built to do.

If you start this code from the Start_Macro procedure, you are declaring the 'flag' as False, you then call the 'Rerun' procedure.

'Rerun' procedure runs 3 seconds from when it is called

Rerun calls 'save_workbook' & then hits this error.

What is the 'flag' for & what is the code trying to achieve?