save workbooks periodically

[kylefoley76]

My excel crashes a lot and when it does i have to resave and rename all of my workbooks, usually about 7 into their individual folder. i'm tired of this. i'm trying to get excel to save all workbooks periodically. the problem with the following code is that it freezes excel while its running and it puts each workbook to the front one by one when it saves it. i want it to save all workbooks without bringing each workbook to the front and without freezing excel

Sub save_all_books_periodically()




Dim a, i&, fn$
ReDim a(1 To Workbooks.Count)


For k = 1 To 2
For i = 1 To Workbooks.Count
'a(i) = Workbooks(i).Save
Workbooks(i).Save
Next i
Application.Wait (Now + TimeValue("0:00:10"))
Next k



End Sub

 

[Yongle]

Try this

- place the code in a standard module
- to start the code call StartSave (which resets boolean variable CancelSave to FALSE)
- to stop the code call StopSave

Public CancelSave As Boolean

Sub save_all_books_periodically()
    Dim k As Integer
    If CancelSave Then
        MsgBox "periodic save stopped by user"
        Exit Sub
    Else
        For k = Application.Workbooks.Count To 1 Step -1
            Application.DisplayAlerts = False
                If Workbooks(k).Name <> ThisWorkbook.Name Then Workbooks(k).Save
            Application.DisplayAlerts = True
        Next k
        
        Application.OnTime Now + TimeValue("00:00:05"), "save_all_books_periodically"
    End If
End Sub

Sub StartSave()
    CancelSave = False
    Call save_all_books_periodically
End Sub

Sub StopSave()
    CancelSave = True
End Sub

Will this code be run from a master workbook?
Terminate the code when master is closed with..

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call StopSave
End Sub

Start thr code when master is opened with ..

Private Sub Workbook_Open()
    Call StartSave
End Sub

 

[kylefoley76]

Thanks it certainly works a lot better. It's tolerable but it put another workbook frontmost during the save. is there anyway to stop that?

 

[kylefoley76]

Also, I can't stop the macro once it is started.

 

[Yongle]

it put another workbook frontmost during the save. is there anyway to stop that?

try this

Sub save_all_books_periodically()
    Application.ScreenUpdating = False
    Dim k As Integer,[COLOR=#ff0000] wb As Workbook:    Set wb = ActiveWorkbook[/COLOR]
    For k = Application.Workbooks.Count To 1 Step -1
        Application.DisplayAlerts = False
            If Workbooks(k).Name <> ThisWorkbook.Name Then Workbooks(k).Save
        Application.DisplayAlerts = True
    Next k
    If CancelSave Then
        MsgBox "periodic save stopped by user"
        Exit Sub
    End If
    [COLOR=#ff0000]wb.Activate
[/COLOR]    Application.ScreenUpdating = True
    Application.OnTime Now + TimeValue("00:00:10"), "save_all_books_periodically"
End Sub

 

[Yongle]

I can't stop the macro once it is started

Did you declare boolean variable CancrelSave at top of standard module before all procedures?

Public CancelSave As Boolean

 

[kylefoley76]

Thanks that's a real improvement. Now the only problem is that when another app is frontmost such as chrome, excel will come to the front. do you know how to stop that?

Did you declare boolean variable CancrelSave at top of standard module before all procedures?

Public CancelSave As Boolean

no i hadn't, but now i have and that solved the problem.

 

[Yongle]

Now the only problem is that when another app is frontmost such as chrome, excel will come to the front. do you know how to stop that?

Strangely that does not happen on my laptop - Excel always remains in the background.
I am running Windows 10 and Office 2016. How about you?

Sounds complicated and outside Microsoft so perhaps needs an API call.
Perhaps you can figure it out - this link may help