Excel autosave 1004 error with temporary file

Question

We have a .xlsm that autosaves every 30 seconds (created using the solution from here).

But every so often, it will run into "1004 error" and say it cannot find a 8-character named temporary file and erros out on the vba line ThisWorkbook.Save (from the simple solution above). Data amount in the sheet is tiny and should not take more than a second to save.

I also have checked "options"->"Save"->"Disable AutoRecover for this workbook only", which seems to marginally alleviate but not solve this problem.

Thank you for the help.

[EDIT]: The problem seems to have evolved. Now leaving excel running on its own barely gives the 1004 save error anymore. But if there is another python process using pandas to "read_excel" the file (loop read to trigger the issue), excel will quickly freeze up and pop up a window to save the tmp file (as in excel "save as" function).

It seems that pandas read_excel() locks the excel leading to a conflict when saving, but I don't see a "read_only" parameter. How does one not lock the file when reading it? (this is on windows)

Answer 1

I can tell you more or less what's happening here, although not necessarily how to fix it.

When you save an open workbook in Excel, what actually happens is roughly this:

1. Excel creates a temporary file in the same folder as the open workbook. (This is the temporary file you are seeing in the 1004 error message.)
2. Excel saves the workbook to the newly created temporary file.
3. Excel deletes the existing file (having first released its lock on that file).
4. Excel renames the temporary file to the original file name (and re-locks the file).

This is partly covered by Microsoft's documentation here. You could also read this SuperUser answer or this Q&A on Reddit which describe similar problems.

What's happening in your case is that at some point the temporary file created at step 1 above isn't accessible during a later step when Excel expects it to be.

Possible causes include the following:

• Anti-virus software which monitors newly created files and interferes with or removes the temporary file just as Excel is trying to rename it.
• A similar issue caused by a security rule or tool on your network.
• A problem with another syncing mechanism, perhaps some network service which continually monitors and syncs or backs up your user area.
• A clash with another autosave mechanism in Excel, possibly in an add-in.

You could try disabling antivirus software, Windows Defender or other Excel add-ins (etc) to try to isolate the cause, although this might not be possible if you are working in a corporate environment.

If you really need to identify the exact cause, you are probably going to need to write some rather complex custom code to monitor that folder and find out exactly what's happening to temporary files during saves and which processes have handles to those files. I do not recommend attempting this.

More realistically, I would suggest adding some simple error handling to catch this 1004 error, perhaps retry the save and then abort silently, scheduling the next save as normal. If you are saving the file every 30 seconds, the occasional silent failure should not be a major problem.

Answer 2

I am not sure if this specific issue is what causes the error you encounter, but there is a problem in the auto-save feature you've implemented.

Application.OnTime is as you can tell by how you call it; a method of the Application object. Meaning it is not called from any specific Workbook/sheet, but from the Application Excel itself. Meaning as long as Excel is open it will keep on running.

So if you have a second (unrelated) Workbook open and close the one where you've implemented the auto-save feature, the code for autosaving will just keep on running. If you now open the autosaving Workbook again you'll suddenly have 2 autosave loops running. So 2 autosaves per 30 sec. Do it again and you'll have 3 autosave loops running simultainiously.

The autoSave loop will only terminate if you close Excel completely.

This issue can be fixed, by terminating the OnTime calls before you close the Workbook. You can do this as shown below:

NormalModule:

Global saveTimer As Variant

Sub Save1()
    ThisWorkbook.Save
    saveTimer = Now + TimeValue("00:00:30")
    Application.OnTime saveTimer, "Save1"
End Sub

WorkbookModule:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.OnTime saveTimer, "Save1", , False
End Sub

Private Sub Workbook_Open()
    saveTimer = Now + TimeValue("00:00:30")
    Application.OnTime saveTimer, "Save1"
End Sub

With this code, the currently running OnTime call will be terminated if you close the workbook.

Answer 3

But if there is another python process using pandas to "read_excel" the file (loop read to trigger the issue), excel will quickly freeze up and pop up a window to save the tmp file (as in excel "save as" function).

Based on the error message and the results to the other answers I believe you're running this Excel Macro Enabled Spreadsheet on a server. Unfortunately running spreadsheets unattended on servers is not supported as per Considerations for server-side Automation of Office.

The error is listed:

Run-time error '1004': Method '~' of object '~' failed

Can you try to run this on a Client PC?

If that works leave it running on the PC or make an application, eg .Net or Python that will perform the same Excel operations with XML files with a library like OpenXML or ClosedXML.

Sorry about this, it's not a very well known limitation, it was a popular KB base article; originally KB257757.