auto safe macro for workbook

[pswanie]

i got a workbook that several ppl use. i need the workbook to auto save every
5 or so min. i got a few macro's. dont wana just add it to a macro.
if sumthing hapens in between using the macro's i want the data saved


i work for a company with about 18 branches all over. if i go to tools etc
it will only safe my work on my computer. i email this worksheet to the
other branches and need theyr stuff to auto save as well. i can add a save
code to all my macro's but a powerfail or some happens and they did not run
any macro then all work will be lost

I used the following

Private Sub Workbook_Open()
Application.AutoRecover.Enabled = True
Application.AutoRecover.Time = 5
End Sub


as much as i hate to say it...

that code did not seem to work

i enterd some info and let it “rest†for 40 minites.

when i came back i closed the workbook and upon being asked to save i said
no. (the code should have done that 3 times in the 40 minites that i left)

i reopend the workbook and the info have not been saved..

 

[Tom Ogilvy]

Dave Peterson gave you a full explanation of why it doesn't work as you
expect and how it actually does work. (Autorecover is not the same a saving
the original workbook). He also gives you a link to Chip Pearson's ontime
page which is the approach you can use to do what you want. You will need to
initiate the first ontime in the Workbook_Open event. Here is the text of
Dave's post:

xl2k and below came with an optional addin called AutoSave.xla. It could be
set
to save every x minutes (user selectable). And it just saves the file at
those
intervals.

xl2002+ comes with something called autorecovery. It's also optional, but if
the user turns it on, it saves a copy of that workbook in a special location
(also user selectable). If windows or excel crash, then the next time excel
opens, it notices that there's a file in that location. Excel prompts the
user
to see if he/she wants to recover that file that was saved when excel/windows
crashed.

This autorecovery feature isn't used for the same purpose as AutoSave.

(If you have a copy of autosave.xla from a previous version, it'll work with
xl2002+, too.)

But since you don't want to have the user select options to turn on autosave,
you may find adding an ontime macro that runs every so often to your
workbook's
project.

Chip Pearson explains how to use application.ontime:
http://www.cpearson.com/excel/ontime.htm

 

[Dave Peterson]

You have more responses at your other thread.

i got a workbook that several ppl use. i need the workbook to auto save every
5 or so min. i got a few macro's. dont wana just add it to a macro.
if sumthing hapens in between using the macro's i want the data saved

i work for a company with about 18 branches all over. if i go to tools etc
it will only safe my work on my computer. i email this worksheet to the
other branches and need theyr stuff to auto save as well. i can add a save
code to all my macro's but a powerfail or some happens and they did not run
any macro then all work will be lost

I used the following

Private Sub Workbook_Open()
Application.AutoRecover.Enabled = True
Application.AutoRecover.Time = 5
End Sub

as much as i hate to say it...

that code did not seem to work

i enterd some info and let it “rest†for 40 minites.

when i came back i closed the workbook and upon being asked to save i said
no. (the code should have done that 3 times in the 40 minites that i left)

i reopend the workbook and the info have not been saved..

 

[pswanie]

i used the code from that link but got a error. i guess its the 'initiate
the first ontime that i need to add. how do i go about with that? do i just
copy that code to a module? this is wat i did:

Sub saveworkbook()
'
' saveworkbook Macro
' Macro recorded 2007/06/17 by Knysna
'

'
ActiveWorkbook.Save
End Sub




Public RunWhen As Double
Public Const cRunIntervalSeconds = 300 ' two minutes
Public Const cRunWhat = "saveworkbook"


i then get:

compile error

only comments... etc


thanx

 

[Dave Peterson]

I'd remove any code you've started.

Then put this in a general module (not behind ThisWorkbook, not behind a
Worksheet):

Option Explicit
Public RunWhen As Double
Public Const cRunIntervalSeconds = 10 '10 seconds for testing
Public Const cRunWhat = "The_Sub"
Sub StartTimer()
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _
schedule:=True
End Sub
Sub StopTimer()
On Error Resume Next
Application.OnTime earliesttime:=RunWhen, _
procedure:=cRunWhat, schedule:=False
End Sub
Sub The_Sub()
ThisWorkbook.Save
Call StartTimer
End Sub
Sub Autpen()
Call StartTimer
End Sub
Sub Auto_Close()
Call StopTimer
End Sub

 

[pswanie]

now that resolved my problem!!

thanx for ur help
do appreciate..

phillip

 

[Tom Ogilvy]

Insert a module in you workbook (in the VBE, Insert=>Module)

Put this code in it. (the Variable declarations need to be at the top above
any Procedure declarations).

Public RunWhen As Double
Public Const cRunIntervalSeconds = 300 ' five minutes
Public Const cRunWhat = "The_Sub"


Sub StartTimer()

RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _
schedule:=True
End Sub



Sub The_Sub()
'
ThisWorkbook.Save
'
StartTimer

End Sub

Then in the ThisWorkbook MOdule

Private Sub Workbook_Open()
StartTimer
End sub