CODEWITHSUNDEEP
excelvbadatetimetime
DavidJB
DavidJB

Reputation: 2362

Save excel file every 30 seconds using system time

I would like to save an excel file every 30 seconds based on the current system time. So at HH:MM:30 and HH:NN:00. I'm aware you can call a macro (to save the workbook) at specific times using TimeValue, for example at 16:30:00 (see below). Does anyone know if this, or another function, can be used to save an open workbook when the current system time updates to XX:XX:30 or XX:XX:00 in excel. 

Private Sub Workbook_Open()

Application.OnTime TimeValue("16:30:00"), "macro_save"

End Sub

Upvotes: 2

Views: 12622

Answers (1)

mkingston
mkingston

Reputation: 2718

In your workbook code:

Private Sub Workbook_Open()
    If Second(Now) < 30 Then
        Application.OnTime VBA.TimeSerial(Hour(Now), Minute(Now), 30), "macro_save"
    Else
        Application.OnTime VBA.TimeSerial(Hour(Now), Minute(Now) + 1, 0), "macro_save"
    End If
End Sub

In a standard module:

Public Sub macro_save()
    ThisWorkbook.Save
    If Second(Now) < 30 Then
        Application.OnTime VBA.TimeSerial(Hour(Now), Minute(Now), 30), "macro_save"
    Else
        Application.OnTime VBA.TimeSerial(Hour(Now), Minute(Now) + 1, 0), "macro_save"
    End If
End Sub

You'll probably need to handle errors in the macro_save code, otherwise it could get pretty (very) annoying for the user.

Upvotes: 5

Related Questions