CODEWITHSUNDEEP
excelvba
Alexander Chervov
Alexander Chervov

Reputation: 934

How to save an Excel file every, say one minute?

Are there some macros which can do it?

Upvotes: 3

Views: 33990

Answers (2)

Lord-JulianXLII
Lord-JulianXLII

Reputation: 1249

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.

Upvotes: 3

Kresimir L.
Kresimir L.

Reputation: 2441

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.

Upvotes: 14

Related Questions