Macro to replicate auto-save?

Posted by patvanaalst PM | on Wed, 28/03/2012 - 09:55 1496 3 comments | report

Morning all

I'm having issues with users not saving workbooks regularly and have found the Microsoft appear to have eliminated Autosave, we're using 2007. Autorecover is no help as it removes any backups once you close Excel. Is there a way of using another tool, such as a macro, to save a workbook regularly?

I've taken a look online and it seems to be there, in the form of OnTime, but this looks like it will open a closed workbook to save (which will be no good as the number of workbooks will grow with time) and also seems to be date driven (which would need to be infinate).

Can anyone help please?

Many thanks

Pat

Comments

How about a workbook event?

paulwakefield1 | Wed, 28/03/2012 - 10:20 | Permalink

This (cribbed shamelessly from the Excel helpfile) will save an unsaved workbook on closing:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Me.Saved = False Then Me.Save
End Sub

Enter in a macro enabled workbook in the ThisWorkbook object

Not helpful for you I know but this issue is solved in Excel 2010:

Under Excel options - Save, ensure that "Keep the last Autosaved version if I close without saving" is ticked.

Thanks

patvanaalst | Wed, 28/03/2012 - 13:58 | Permalink

We're at the 'make do and mend' phase of business at the moment so 2007 will have to remain!!! I use 2010 at work and it is definately a step up from the mistake of 2007...

Thanks also to Jason @ Pendragon for jumping on this and sending me an add in. If I'm a good boy I'll report back on how it's performed, but I'm prone to forgetfulness I'm afraid!

Thanks all

Pat