Save .xlsm every 15 minutes

MSingh
3StarLounger
Posts: 366
Joined: 12 May 2010, 06:49

Save .xlsm every 15 minutes

Post by MSingh »

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

User avatar
HansV
Administrator
Posts: 78229
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Save .xlsm every 15 minutes

Post by HansV »

Place the following macro in a standard module:

Code: Select all

Public dtmTime As Date

Sub SaveMe()
  ThisWorkbook.Save
  dtmTime = Now + TimeSerial(0, 15, 0)
  Application.OnTime dtmTime, "SaveMe"
End Sub
And the following code in the ThisWorkbook module:

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
You may also want to take a look at Jan Karel Pieterse's free AutoSafe add-in - see JKP-ADS Download page.
Last edited by HansV on 16 Aug 2010, 08:19, edited 2 times in total.
Reason: to correct oversight
Best wishes,
Hans

MSingh
3StarLounger
Posts: 366
Joined: 12 May 2010, 06:49

Re: Save .xlsm every 15 minutes

Post by MSingh »

Hi Hans,

Thank you - i left the code to run for a while, it works perfectly-many thanks

Regards
Mohamed

User avatar
HansV
Administrator
Posts: 78229
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Save .xlsm every 15 minutes

Post by HansV »

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:

Code: Select all

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  On Error Resume Next
  Application.OnTime dtmTime, "SaveMe", , False
End Sub
Best wishes,
Hans

MSingh
3StarLounger
Posts: 366
Joined: 12 May 2010, 06:49

Re: Save .xlsm every 15 minutes

Post by MSingh »

Thank You Hans,

I'll put that in.

Kind Regards
Mohamed

User avatar
Jan Karel Pieterse
Microsoft MVP
Posts: 656
Joined: 24 Jan 2010, 17:51
Status: Microsoft MVP
Location: Weert, The Netherlands

Re: Save .xlsm every 15 minutes

Post by Jan Karel Pieterse »

Note that in the situation Hans describes (user cancels the close), the saving scheme is stopped.
Regards,

Jan Karel Pieterse
Excel MVP jkp-ads.com

MSingh
3StarLounger
Posts: 366
Joined: 12 May 2010, 06:49

Re: Save .xlsm every 15 minutes

Post by MSingh »

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