Hi,
How can i save myfile.xlsm such that it saves every fifteen minutes (& not re-opens the workbook if closed then saves).
Thanks again
Mohamed
Save .xlsm every 15 minutes
-
- Administrator
- Posts: 78229
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Save .xlsm every 15 minutes
Place the following macro in a standard module:
And the following code in the ThisWorkbook module:
You may also want to take a look at Jan Karel Pieterse's free AutoSafe add-in - see JKP-ADS Download page.
Code: Select all
Public dtmTime As Date
Sub SaveMe()
ThisWorkbook.Save
dtmTime = Now + TimeSerial(0, 15, 0)
Application.OnTime dtmTime, "SaveMe"
End Sub
Code: Select all
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime dtmTime, "SaveMe", , False
End Sub
Private Sub Workbook_Open()
dtmTime = Now + TimeSerial(0, 15, 0)
Application.OnTime dtmTime, "SaveMe"
End Sub
Last edited by HansV on 16 Aug 2010, 08:19, edited 2 times in total.
Reason: to correct oversight
Reason: to correct oversight
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 366
- Joined: 12 May 2010, 06:49
Re: Save .xlsm every 15 minutes
Hi Hans,
Thank you - i left the code to run for a while, it works perfectly-many thanks
Regards
Mohamed
Thank you - i left the code to run for a while, it works perfectly-many thanks
Regards
Mohamed
-
- Administrator
- Posts: 78229
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Save .xlsm every 15 minutes
The Workbook_BeforeClose event is used to cancel the scheduled run of SaveMe; in order to cancel, you need to know the scheduled time. The global variable dtmTime is used to keep track of this time.
I found a potential problem: if the user edits the workbook after it has been saved automatically, then starts to close it, the scheduled run will be canceled. The "save changes" dialog will appear. If the user clicks Cancel, the workbook will remain open. Next time the user starts to close the workbook, the Worksheet_BeforeClose event procedure will try to cancel the scheduled run again, but since there is no scheduled run anymore, this will cause an error. To prevent this, change the BeforeClose event procedure as follows:
I found a potential problem: if the user edits the workbook after it has been saved automatically, then starts to close it, the scheduled run will be canceled. The "save changes" dialog will appear. If the user clicks Cancel, the workbook will remain open. Next time the user starts to close the workbook, the Worksheet_BeforeClose event procedure will try to cancel the scheduled run again, but since there is no scheduled run anymore, this will cause an error. To prevent this, change the BeforeClose event procedure as follows:
Code: Select all
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.OnTime dtmTime, "SaveMe", , False
End Sub
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 366
- Joined: 12 May 2010, 06:49
Re: Save .xlsm every 15 minutes
Thank You Hans,
I'll put that in.
Kind Regards
Mohamed
I'll put that in.
Kind Regards
Mohamed
-
- Microsoft MVP
- Posts: 656
- Joined: 24 Jan 2010, 17:51
- Status: Microsoft MVP
- Location: Weert, The Netherlands
Re: Save .xlsm every 15 minutes
Note that in the situation Hans describes (user cancels the close), the saving scheme is stopped.
-
- 3StarLounger
- Posts: 366
- Joined: 12 May 2010, 06:49
Re: Save .xlsm every 15 minutes
Hi Jan,
Thanks for your advice.
And for pointing me to this forum. Solutions found here would have taken me months to work thro'.
Kind Regards
Mohamed
Thanks for your advice.
And for pointing me to this forum. Solutions found here would have taken me months to work thro'.
Kind Regards
Mohamed