2

Are there some macros which can do it?

Any help would be appreciated - I am novice with VBA.

RobertBaron
  • 2,817
  • 1
  • 12
  • 19
Alexander Chervov
  • 616
  • 3
  • 7
  • 23
  • 1
    https://www.reddit.com/r/excel/comments/3i6hf8/code_in_vba_to_automatically_save_a_file_every_15/ – John Coleman Oct 02 '17 at 11:23
  • 1
    Excel has an [option](https://support.office.com/en-us/article/Help-protect-your-files-in-case-of-a-crash-551c29b1-6a4b-4415-a3ff-a80415b92f99#ID0EAAEAAA=2016,_2013,_2010) to auto-save every X minutes. – MatSnow Oct 02 '17 at 11:30
  • @MatSnow Thank you for your comment ! I do not quite understand where the autosave file is saved ? Where can I find it ? I mean I see that original file is NOT saved every minute even if I put autosave = 1 - probably Excel saves copy to some its internal directory – Alexander Chervov Oct 02 '17 at 11:42
  • 1
    @AlexanderChervov Excel saves auto-recovery information in `%APPDATA%\Microsoft\Excel` by default. Path can be changed in options. – MatSnow Oct 02 '17 at 12:06
  • @MatSnow Thank you very much for your advise ! There are several issues I cannot resolve 1 ) AutoSave fies have extension .xlsb - and Python Pandas CANNOT open it 2) It seems that autosave does really works in the way I understand - I do not see new files each minute it seems it creates several files (5) and after 5-th is saved it rewrites file number one – Alexander Chervov Oct 03 '17 at 08:35
  • @AlexanderChervov If you need to open the files with another software than Excel, then KresimirL.'s answer is the way to go. – MatSnow Oct 03 '17 at 08:52

2 Answers2

14

Please put this code in ThisWorkbook module. you can access this module by pressing double click on ThisWorkbook module inside VBA project. 

Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("00:01:00"), "Save1"
End Sub

enter image description here

then put this code in standard module. To insert standard module, right click on VBA project==>Insert==>Module. then paste code in newly created module.

enter image description here

Sub Save1()
Application.DisplayAlerts = False
ThisWorkbook.Save
Application.DisplayAlerts = True

Application.OnTime Now + TimeValue("00:01:00"), "Save1"
End Sub

Now when you open your workbook, it will automatically save every minute. You need to reopen Workbook in order to trigger Workboook_Open event.

Kresimir L.
  • 2,301
  • 2
  • 10
  • 22
  • Thank you very much for your kind and fast answer ! If it is possible , would you be so kind to comment what means "ThisWorkbook module" and "standard module", I am not sure I understand VBA basics. I thought one needs to create button and attach macroses to buttons, but it seems you are not using that way. – Alexander Chervov Oct 02 '17 at 11:36
  • 1
    HI, please see edited answer. you can also find mora about it [here:](https://stackoverflow.com/questions/12955461/what-difference-does-it-make-if-one-runs-a-vba-code-in-sheets-in-thisworkboo) could you please accept answer? – Kresimir L. Oct 02 '17 at 11:45
  • Thank you very much ! I will accept your answer, but still I have one more issue - can I start the module without REopening the file ? Something like "start" button ? Because - if I save file it will be *.xlsm, but the stream of data which I need to store does not support that kind of file – Alexander Chervov Oct 02 '17 at 12:11
  • You can assign Save1 macro to a button and then run it, but you will still need to save file as .xlsm or .xls or .xlsb in order to preserve macro after you exit workbook. – Kresimir L. Oct 02 '17 at 12:14
  • Would you be so kind to look at: https://stackoverflow.com/questions/46550958/how-to-save-all-excel-files-every-say-minute-10-seconds – Alexander Chervov Oct 03 '17 at 18:18
  • Application.OnTime is a method of the Application object. hence the autosave loop will only terminate once Excel gets closed (not the Workbook). Leading to potentially having multiple autosave loops running simultainioulsy (if you reopen the workbook). To avoid this you should terminate OnTime calls before you close the workbook (see my answer below) – Lord-JulianXLII Apr 28 '23 at 13:13
  • Your solution will fail if another user opens the file while you have it open. You need to handle when the file is opened as Read Only. – Frank Ball Apr 28 '23 at 13:59
3

The accepted answer while working has a problem:

Application.OnTime is as you can tell by how you call it; a method of the Application object. Meaning it is not called from any specific Workbook/sheet, but from the Application Excel itself. Meaning as long as Excel is open it will keep on running.

So if you have a second (unrelated) Workbook open and close the one where you've implemented the auto-save feature, the code for autosaving will just keep on running. If you now open the autosaving Workbook again you'll suddenly have 2 autosave loops running. So 2 autosaves per 30 sec. Do it again and you'll have 3 autosave loops running simultainiously.

The autoSave loop will only terminate if you close Excel completely.

This issue can be fixed, by terminating the OnTime calls before you close the Workbook. You can do this as shown below:

NormalModule:

Global saveTimer As Variant

Sub Save1()
    ThisWorkbook.Save
    saveTimer = Now + TimeValue("00:00:30")
    Application.OnTime saveTimer, "Save1"
End Sub

WorkbookModule:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.OnTime saveTimer, "Save1", , False
End Sub

Private Sub Workbook_Open()
    saveTimer = Now + TimeValue("00:00:30")
    Application.OnTime saveTimer, "Save1"
End Sub

With this code, the currently running OnTime call will be terminated if you close the workbook.

Lord-JulianXLII
  • 1,031
  • 1
  • 6
  • 16