Save a copy of the Excel file to another location every 15 seconds

sofas

Active Member
Joined
Sep 11, 2022
Messages
452
Office Version
  1. 2019
Platform
  1. Windows
Hello everyone, I have an excel file that consists of two sheets. I want the file to be automatically saved every 15 seconds and to save another copy of it in another place with the same name with the day and hour of saving. Thank you.
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
In a regular module :

VBA Code:
Option Explicit


Sub shwMsg()
    
    Application.OnTime Now + TimeValue("00:00:15"), "sveMe"  '<---- change time delay here. Currently set to 5 minutes.
    
End Sub

Sub sveMe()

Application.DisplayAlerts = False
    ThisWorkbook.Save
    sveWrkBk
Application.DisplayAlerts = True
    shwMsg
    
End Sub

Sub stopMacros()
    Exit Sub
End Sub




'saves file every 15 secs
Sub sveWrkBk()
Dim FName           As String
Dim FPath           As String
Dim dt              As String
Dim wb              As String

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    wb = ActiveWorkbook.Name
    'ActiveWorkbook.SaveAs Filename:= _
    '    "F:\Documents and Settings\Tim\My Documents\" & wb & ".cen", FileFormat:=xlText, _
    '    CreateBackup:=False
    
    dt = Format(Now(), "mm-dd-yyyy hh mm")
    FPath = "C:\Users\jimga\Desktop\"       ''<--- Change SAVE DIRECTORY as required
    FName = wb & dt                         ''<--- Change name if required .. just this part / Auto Backup File - \
    ThisWorkbook.SaveCopyAs Filename:=FPath & FName & ".xlsm"
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    
End Sub


In the ThisWorkbook module :

Code:
Option Explicit

Private Sub Workbook_Open()
    shwMsg
End Sub

Download workbook : AutoSave w DateTime Name Every 15 secs.xlsm
 
Upvote 0
Solution
This line : Application.OnTime Now + TimeValue("00:00:15"), "sveMe" '<---- change time delay here. Currently set to 5 minutes.

should say "Currently set to 15 seconds.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,266
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top