How to back up excel file at regular interval?

[volabos]

Dear all, I have an excel file which I am working on currently and it is saved in c: drive (say). Now I want to create a backup mechanism which should be run once a particular time each day and backup the latest updated copy and that backup-ed file should be placed in some other location.

I want to do that as I am constantly modifying that file, have a fear may be in last session I might have done something wrong and therefore should be able to get the most recent back copy, which is not older than 1 day.

Is there any way to do that? I would be really grateful I somebody point me any help on this.

Thanks for your time.

[Zack Barresse]

There really is no in-built way to do this. There is a native AutoRecovery feature, but it doesn't save a copy of the file, but is only there if the file crashes. For an actual autosave feature, you'll need VBA for that. Here are a couple of links which might help:

http://www.vbaexpress.com/kb/getarticle.php?kb_id=265

http://www.oaltd.co.uk/MVP/Default.htm (I believe the last supported version is 2003, so don't know if it works on anything beyond that)

[MRichmond]

I found a brilliant piece of code on this site a while ago, and whilst it doesn't save automatically at a certain time, it does save a copy elsewhere whenever you press the save button, and it also works if the file is on a shared drive and other people save as well (of course the autosave destination would also have to be shared).

Private Sub workbook_beforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim sFile
Dim location As String
Application.EnableEvents = False
location = "C:\Desktop\Archive\" 'change to suit
sFile = Replace(ThisWorkbook.Name, ".xls", " Backup ") & Format(Now, "yyyymmdd hh-mm-ss")
ThisWorkbook.SaveCopyAs location & sFile & ".xls"
Application.EnableEvents = True
End Sub