excel background save via vba

Question

The Workbook.Save line in my macro is holding everything up, and while it's important that there's a save step at the end of the macro, I don't mind if it just starts saving and then hands control back to the user.

Is there such a thing as Workbook.Save BackGround or Workbook.Save vbModeLess?

Answer 1

When excel saves a file it created a temporary file with a name like A82732KS.tmp and the quickly delete the original file and rename the temp file (possibly in an atomic operation). To do this excel has to release control of the file to avoid a sharing violation so it necessarily disables any changes in memory in order to guarantee that was is written on file and what is loaded in memory is identical.

Answer 2

Is there such a thing as Workbook.Save BackGround or Workbook.Save vbModeLess?

Definitively, no. The full list of methods available to the workbook object:

http://msdn.microsoft.com/en-us/library/office/ff847316(v=office.14).aspx

The .Save method does not have any optional arguments:

http://msdn.microsoft.com/en-us/library/office/ff197585(v=office.14).aspx

It seems you are perceiving a "problem" with your code which is not actually a problem, but normal and expected functionality, as I explained in the comments above:

• When a user manually saves the file, the application is not interactive. The user can't do anything except wait for the save to finish.

• The same occurs when you invoke the .Save method of the workbook object, or the Application.CommandBars.ExecuteMso "FileSave", etc.

This is necessary because (obviously) changes made while saving would not be saved, but the workbook's .Saved property would display True.

This property is used in determining whether to show the "Close this workbook with unsaved changes?" dialog when the user closes the file. If the property is True, then the user can close without any prompt. But of course if you let them make changes this will inevitably lead to unwanted data loss as the user may then close the file with saved state True and unsaved changes to the workbook which have not been reflected in the Saved property.

(Note: there are probably more technical reasons, too, but this is just the common-sense explanation)

If the length of time it takes to save the file is burdensome, you have at least a few options I can think of, first you would want to consider notifying the user that the file is going to be saved and this may take upwards of 45 seconds. This way, they do not think the program is unresponsive or otherwise hanging. You can do this with a MsgBox or a UserForm pretty easily.

Alternatively, you could use either of the above methods to prompt the user, i.e., "Do you want to save the file?"

Etc.