save every 10 minutes in Excel 2010

[pendle666]

Hi
There's been a problem where my excel spreadsheet seems to become corrupted and I can no longer open it - the error refers to "rights access policy" set on the machine and IT Support are looking at it.

Is it possible to get a small macro which will physically save another copy every 10 minutes or so? I've had to rely on a backup to get my work back and try and remember what changes were made, which depending on what I'm doing could be quite a few!


Pendle

 

[JasonEnsor]

Hi Pendle666,

I use the following Macro attached to my Ribbon to create a backup, I am sure it can easily updated to reply on a time period instead of a button click.

Sub Create_Backup(control As IRibbonControl)
    ActiveWorkbook.SaveCopyAs FileName:=ThisWorkbook.Path & "\" & Format(Date, "DD-MM-YY") & " " & ActiveWorkbook.name
End Sub

Hope that helps

Jason

 

[JasonEnsor]

Something like this should work for you

Sub Create_Backup()
    Application.OnTime Now + TimeValue("00:10"), "Create_Backup"
    ActiveWorkbook.SaveCopyAs FileName:=ThisWorkbook.Path & "\" & Format(Date, "DD-MM-YY") & " " & ActiveWorkbook.name
End Sub

Then in your Workbook_Open procedure place a call to the Create_Backup. You can amend this to use a timestamp instead of a date.

Hope that helps

Jason.

 

[pendle666]

Many thanks for that, I shall give it a try.

 

[strongm]

You might want to have a look at Excel's Autosave feature under File/Options/Save

 

[pendle666]

Hello

I've had to come back to this topic as I'm having continued problems with this access rights management issue.

I currently have the following

Sub SpecTempsBackup()

ActiveWorkbook.SaveCopyAs Filename:="J:\BACKUPS" & "\" & Format(Date, "DD-MM-YY") & " " & ActiveWorkbook.Name
End Sub

I have to run this manually, I couldn't get it to make a backup automatically. But if I rerun this on the same day, it gives an error message because the file already exists with the date and filename.

Please could some kind person, add in the extra bit I need to make it a) autosave every 30 minutes and b) give it a date/time in the filename, no matter what I tried, it wouldn't accept any kind date of date/time format.

thank you very much
Pendle

 

[Andrzejek]

tRY:
[TT]
:="J:\BACKUPS" & "\" & Format([blue]Now, "MM-DD-YYYY_HH-MM-SS"[/blue])[/TT]

Have fun.

---- Andy

 

[pendle666]

Hello

That won't work for me, it now saves the file with the date time (had to put it in as HH:MM:SS rather than HH-MM-SS), however if I run the macro again I get the dialog box saying that it can't find the file and to try again. It's as though it's not saving a copy of the workbook. I need to have backup copies on a regular basis because the activex buttons that are attached to it keep corrupting.

regards

Pendle

 

[JasonEnsor]

Hi Pendle

You can't use ':' in a filename. Maybe that is what is causing part of your issue

Regards

Jason

 

[pendle666]

Excellent - that was all it was!! Thank you very much for replying to my message and apologies for taking so long to come back with a response.

Now all I need to do is add in the timed element. This was suggested before:

Application.OnTime Now + TimeValue("00:10"), "Create_Backup"

So the entire macro would look like this:

Sub SpecTempsBackup()
Application.OnTime Now + TimeValue("00:10"), "Create_Backup"
ActiveWorkbook.SaveCopyAs Filename:="J:\BACKUPS" & "\" & Format(Now, "MM-DD-YYYY HH-MM-SS") & " " & ActiveWorkbook.Name
End Sub



regards
Pendle