autosave macro in excel

[Eoll]

First of all, I really need to say, I love this forum!
I love the way you can search, make favourite threads, the replies, I love it all!
(so much for the sucking up, now i need help! ;-))
After having read about 20 threads, i still can't seem to find/create the right code.
What i want to do is create an autobackup macro that will run in the background of a shared excel workbook (it's a bookinglist at our gym. Several people can book in appointments with the various trainers, so we don't want to miss info through it crashing, thus double booking people)
As we are on terminal servers with lots of limitations, i want to create a macro that saves the file, say bookinglist.xls to a directory named \backup\bookinglistbackup.xls.

The code i have so far is:

Sub autobackup()
ActiveWorkbook.AcceptAllChanges Who:="Everyone" 
' not sure this one works as i get an error message when it's not shared but cant run vba when it's shared.... no errors on running it in shared mode though...

Application.DisplayAlerts = False
Workbooks("bookinglist.XLS").Save 
'to first save the current changes

Workbooks("bookinglist.XLS").SaveAs Filename:="\backup\bookinglist.xls", FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=True 
' saves the backup file

Application.DisplayAlerts = True
Workbooks.Open "G:\Felles\Fitness 

Flor\stijn\kondis.xls" 
'opens the original again since we reverted to the backup file

Workbooks("kondisbackup.XLS").Close SaveChanges:=False 
'closes the backup
End Sub

what i need help with is automating this so it runs say twice a day, and making this macro run in the background as soon as it's opened....
Please accompany code with comments as i am a complete VBA newb, physical therapist, dutch, and a guy to boot...
All help appreciated..

 

[Eoll]

Found a nice workaround myself. Here's the code for anyone who's interested. Professionals, please comment if there's any obvious mistakes/ future problems which i haven't met yet ;-)

Private Sub Auto_Open()
MsgBox "Autosave will run in the background!"
Application.OnTime TimeValue("10:00:00"), "autobackup"
Application.OnTime TimeValue("14:00:00"), "autobackup"
Application.OnTime TimeValue("18:00:00"), "autobackup"
Application.OnTime TimeValue("22:00:00"), "autobackup"
End Sub

Sub autobackup()
'fungerer bare hvis arket er delt
Workbooks("kondis.XLS").AcceptAllChanges Who:="Everyone"
Application.DisplayAlerts = False
Workbooks("kondis.XLS").Save
Workbooks("kondis.XLS").SaveAs Filename:="c:\Temp\kondisbackup.xls", FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=True
Application.DisplayAlerts = True
Workbooks.Open "G:\Felles\Fitness Flor\stijn\kondis.xls"
Workbooks("kondisbackup.XLS").Close SaveChanges:=False

End Sub

 

[Enkay62]

Hi.
Three suggestions "on the fly".
1) You'd better use workbookopen event insted of auto_open macro.
2)What happens if (for any reason) your program is closed at 21.59 and opened again at 10.01 the morning after?
So, perhaps a safer solution would be to run Sub autobackup
both in workbookopen event and in workbookbeforeclose event.
As for intermediate times, If I were you I would use a timer or, continuing with Ontime method, I would use Ontime now + interval (4 ours) so as not to depend on exact time values but just on exact intervals.
3) Why not saving your workbook directly in "G" without passing through Temp ?
Hope this helps.
Regards
Nick

 

[Eoll]

thanks for the reply!
just some questions in regard to your reply, what is the difference between the auto open and the workbookopen? I put the macro in the personal.xlt on just one pc. Dont know what will happen if it starts making backups on all the workstations at the same time.
the tip in regard to the onworkbookclose is great! will most certainly use that one, same goes for the intervall instead of the ontime statement.
the c:\temp instead of g I already picked up...
thanks for having taken a look at it..
regards

 

[KenWright]

Just curious, but if you have access to a copy of XL2000 then have you considered just using the Autosave addin. I believe the addin can be used in 2002 /2003 quite happily, and was designed for this very thing.

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
---------------- Click here to help the tsunami victims ----------------

 

[Eoll]

autosave does exactly that, it autosaves.... it doesnt however make a backup copy in a different file... that's what "they" wanted so that's what i made...
thanks for the tip though.
btw how do you change the autosave intervalls?

 

[KenWright]

Just click the option and you should get a list of things you can change.

As far as what you are trying to do with code, take a look at Jan Karel's free Autosafe addin which does all you are aiming for and more:-

http://www.jkp-ads.com/Download.htm

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------