Autosave Every 5 minutes.......

Guitarfool5931

Board Regular
Joined
Oct 6, 2008
Messages
83
I'm trying to write a macro in excel that will save the document every couple of minutes. After searching the forums here for a bit I found something that might work:

Sub test()
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 30
waittime = TimeSerial(newHour, newMinute, newSecond)


Do
ActiveWorkbook.Save
Loop

End Sub

The only thing about this is that it runs constantly and won't stop saving. Is there a way to do this where it will only save every 5 minutes or so???
 
I'll check that out. I deleted the code from my macro list but now I get a message saying that it can't run the macro because it can't be found or macros are disabled. Is there a way to stop this?
 
Upvote 0

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Did you delete all instances of the code or is there some hidden in other worksheets? That could also be causing it to double up the saves.
 
Upvote 0
Did you delete all instances of the code or is there some hidden in other worksheets? That could also be causing it to double up the saves.


Well when I press the Macro button nothing appears on the list for it. Even when the all workbooks option is checked
 
Upvote 0
Hi, 'm new to this forum and attain intermediate level in excel VBA. This autosave for open event looks fine but i want to use it in the shared file. what if the excel would be saved multiple time as each user open the workbook or what would happen if many user of the shared file close and open the file frequently. ???
 
Upvote 0
Put this code in the ThisWorkbook module

Code:
Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("00:05:00"), "SaveThis"
End Sub

And, put this code in a standard module (e.g. Module1)
Code:
Sub SaveThis()
Application.DisplayAlerts = False
ThisWorkbook.Save
Application.DisplayAlerts = True

Application.OnTime Now + TimeValue("00:05:00"), "SaveThis"
End Sub

Hi,
I am using this exact code some where I found in the internet...
the problem i having now is even after i close the workbook (or excel), it still opens up every 5minutes and save it...
any fix?
thanks
 
Upvote 0
I am using MS 10
Put this code in the ThisWorkbook module

Code:
Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("00:05:00"), "SaveThis"
End Sub

And, put this code in a standard module (e.g. Module1)
Code:
Sub SaveThis()
Application.DisplayAlerts = False
ThisWorkbook.Save
Application.DisplayAlerts = True

Application.OnTime Now + TimeValue("00:05:00"), "SaveThis"
End Sub

Hello Sir,

I am using MS Office 10

1. I created 1 file with the above code. In STANDARD MODULE code, i changed "ThisWorkbook" to "ActiveWorkbook"
2. As per my understanding, it should save all open workbooks every 5 minute. BUT for me it is saving only the file in which I have inserted this code

My doubt?
1. Am I doing wrong. I don't want to save above code in my all excel file. So i changed it to ActiveWorkbook. How can I achieve this?
2. Is it necessary at the end of 05.00 minute in above code, file which is on screen will save, and rest will not?
 
Upvote 0
2. As per my understanding, it should save all open workbooks every 5 minute.
Actually it does not, since there could be just one "ActiveWorkbook" at a time.
In this case btw, it wouldn't matter if "ThisWorkbook" or "ActiveWorkbook" was used because of the fact, the OnTime method temporarily activates the workbook containing the scheduled macro, launches it, and sets Excel back to its prior state afterwards. Refering to the above code, the active workbook would always be the same as the "ThisWorkbook".
As observed by other forum members, the scheduled code is sometimes executed more often than intended. This is because the scheduled code has to be canceled, preferably at the time the workbook containing the scheduled code is being closed. Otherwise Excel will keep opening the workbook with the scheduled code, will run the code, will close that workbook afterwards, until Excel is completely closed. In order to make canceling possible, the scheduled time has to be stored to be able to let Excel know, which scheduled time has to be cancelled.
Would not recommend you saving all open workbooks using the OnTime method (at such a short interval).
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,923
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top