Autosave workbook

[adamsm]

Hi anyone,

I'm trying to write a macro code that would auto save my workbook every minute.

Any help on this would be kindly appreciated.

Thanks in advance.

 

[VoG]

Hi. Take a look at the OnTime method http://www.ozgrid.com/Excel/run-macro-on-time.htm

 

[adamsm]

Thanks for the help. But how do I place the last save time in cell A1 of any sheet of the workbook?

 

[VoG]

Try in the ThisWorkbook module

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Sheets("Sheet1").Range("A1").Value = Now
End Sub

 

[adamsm]

Thanks for the help.

But with the line you have given I'm getting debug message that highlights the line of code in the This Workbook Module;

Application.OnTime dTime, "MyMacro", , False

How could I avoid this?

Here is the code I'm using on my This Workbook Module

Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("00:01:00"), "MyMacro"
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime dTime, "MyMacro", , False
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Worksheets("NewMemo").Range("D2").Value = Now
End Sub

And Following is the code I've placed in the standard module

Public dTime As Date
Sub MyMacro()
Application.ScreenUpdating = False
dTime = Now + TimeValue("00:01:00")
Application.OnTime dTime, "MyMacro"
'YOUR CODE
ThisWorkbook.Save
 Application.ScreenUpdating = True
End Sub

Any help on this would be kindly appreciated.

 

[Zack Barresse]

Hi there,

Check out this link for the AutoSafe add-in...

http://www.oaltd.co.uk/mvp/Default.htm

HTH

 

[VoG]

I don't know why you would get that error. The code I gave you does not call the Before_Close macro where that line of code is.

 

[adamsm]

Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("00:01:00"), "MyMacro"
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime RunTime, "MyMacro", , False
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Worksheets("NewMemo").Range("D2").Value = Time
End Sub

Public RunTime As Date
Sub MyMacro()
Application.ScreenUpdating = False
RunTime = Now + TimeValue("00:01:00")
Application.OnTime RunTime, "MyMacro"
'YOUR CODE
ThisWorkbook.Save
 Application.ScreenUpdating = True
End Sub

 

[adamsm]

the above post works fine.

 

[Zack Barresse]

I would recommend you looking at the link I provided. Generally I tend to not trust the OnTime macros, as they can break and, as far as I'm concerned, are unreliable for a long-term solution.