Lesson#190: Continuously autosave the workbook with VBA
- Open the VBA editor: In Excel, press Alt + F11 to open the VBA editor.
- Create a new module: In the VBA editor, go to the Insert menu and select Module. This will create a new module where you can write your VBA code.
To create a program in Visual Basic for Applications (VBA) that continuously saves an Excel worksheet at a specified interval, you can use the following code:
Sub AutoSave()
' Set the interval for saving the worksheet (in seconds)
Const Interval As Long = 60 ' 1 minute
' Save the worksheet continuously
Do
ActiveWorkbook.Save
Application.Wait (Now + TimeValue("0:00:" & Interval))
Loop
End Sub
This code defines a subroutine called AutoSave
that continuously saves the active workbook (i.e., the worksheet that is currently open in Excel) at a specified interval. The interval is defined by the constant, which is set to 60 seconds (1 minute) in the example above. You can change this value to any desired interval in seconds.
To use this code, you will need to place the AutoSave
subroutine in a VBA module in your Excel workbook and then call it from another subroutine or from the worksheet’s “Open” event. For example, you could use the following code to start the auto-save process when the worksheet is opened:
Private Sub Workbook_Open()
Call AutoSave
End Sub
This will cause the worksheet to be saved continuously at the specified interval until the workbook is closed.
0 Comments on “Lesson#190: Continuously autosave the workbook with VBA”