Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Anyone have VBA code to Automatically save a workbook every x minutes?

0

I am looking to save a workbook automatically every X mintues if there have been changes made to the workbook. 

I am working with a Shared workbook, if that would be relevent.

I used someones code from another site a few years ago and had to remove it because it would cause an endless Save loop every now and then, that I could not exit from and then if I ended Excel thru the Task Manager, then reopened the workbook, it would still be in the endless loop.

Thank You in advance.

Answer
Discuss

Answers

0
Selected Answer

John

Learn how to do this using one of Don's tutorials on this: Run Macros at Set Intervals in Excel and the more descriptive Run Macros at Set Intervals in Excel. These have the advantage of a Stop code/button to prevent the endless save you mention. 

You'll need to change the time value to "00:03:00" say (3 minutes) and replace the timed task (e.g. MsgBox) with something like this. It's a check to see if the workbook has unsaved changes (via the If) and if so (.Saved=False) then saves without warning (assuming you don't want to be told ):

If ActiveWorkbook.Saved = False Then ActiveWorkbook.Save

(Revision 1: Simplified the code above to a single line)

The net result should be that the code checks every so often (unless you pressed the Stop button) and then it only saves if there have been changes.

Hope this works for you.

Discuss

Discussion

That sounds like what I am looking for. But I wonder, is there a test such as ActiveWorkbook.Changed ? that I could test for new entries into the spreadsheet? Or does the code above check for changes even if the user forgot to save their work?
JohnThro60 (rep: 4) Jan 30, '21 at 11:09 am
John

There's not a Changed property as far as i know but Microsoft say that the ActiveWorkbook.Saved property shows if a workbook has unsaved changes Use Saved property to determine if a workbook has changed.

I'll revise my Answer to remove the Application.DisplayAlerts lines (I was thinking about .SaveAs where a requestor does appear). You might want to record the last autosave date/time in a cell by adding something like this after the save line and changing the destination shown in bold:
Range("K1").Value = Now()
(Suggest you specify the sheet before Range)

If this works for you, please rememeber to mark the Answer as Selected
John_Ru (rep: 6102) Jan 30, '21 at 12:37 pm
I was with you all the way until "Range("K1").Value = Now()"
JohnThro60 (rep: 4) Jan 31, '21 at 8:02 pm
John

That was only a suggestion (so you could see on the spreadsheet when the last save was made). That would be more relevant if you were web scraping (say to get/update a particular price) and wanted to see the date and time when a revision had occurred. You can ignore it, it doesn't affect the solution.

Does the base solution work for you? 
John_Ru (rep: 6102) Feb 1, '21 at 2:14 am
Yes, Except it won't detect if a user has input information and forgot to save their file. But I will keep looking for a solution to that problem in another post. Ty.
JohnThro60 (rep: 4) Feb 1, '21 at 2:41 am
John, it does detect that. Try this test- set the macro running and after a couple of minutes of doing nothing, look in file manager to see when the time was saved. Now change a single cell without manual save. After another couple of minutes, check in file manager and it should show a more recent save time (don't forget to relaunch or refresh file manager, e.g. by going to another folder than back to where you saved. 
John_Ru (rep: 6102) Feb 1, '21 at 3:53 am
Forgot to say, make the gap between changing and checking at least whatever time value you set.

Incidentally you can set the macro to run on opening the workbook (and force it to be opened with macros enabled) by linking the code with events. 
John_Ru (rep: 6102) Feb 1, '21 at 4:18 am
Yes it does. It did not seem to be working like that in some code I was testing. I'm sure it was some other problem that made it seem that way. Anyway - Thanks.
JohnThro60 (rep: 4) Feb 1, '21 at 9:31 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login