How to Automatically Save and Close an Excel File after a Certain Idle Time?


When we save the Excel workbook in shared memory, if one person accesses the sheet, other people will be unable to save the sheet; this problem can be solved by automatically closing the sheet after a certain amount of time. This tutorial will help you understand how we can automatically save and close an Excel file after a certain idle time.

Automatically Save and Close an Excel File after a Certain Idle Time

Here we will first insert VBA code for the sheet, then create a VBA module, then run it to complete our task. Let us see a straightforward process to know how we can automatically save and close an Excel workbook after a certain time. We will be using the help of the VBA application to complete our process.

Step 1

Let us consider any Excel workbook and right-click on the sheet name and select view code to open the vba application. Then, double-click on this workbook and type "Program1" into the text box as shown in the below image.

Example

Dim xTime As String Dim xWB As Workbook Private Sub Workbook_Open() 'Updated by Nirmal On Error Resume Next xTime = Application.InputBox("Please specify the idle time:", "Plese enter the duration", "00:00:30", , , , , 2) Set xWB = ActiveWorkbook If xTime = "" Then Exit Sub Reset End Sub Private Sub Workbook_SheetActivate(ByVal Sh As Object) On Error Resume Next If xTime = "" Then Exit Sub Reset End Sub Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) On Error Resume Next If xTime = "" Then Exit Sub Reset End Sub Sub Reset() Static xCloseTime If xCloseTime <> 0 Then ActiveWorkbook.Application.OnTime xCloseTime, "SaveWork1", , False End If xCloseTime = Now + TimeValue(xTime) ActiveWorkbook.Application.OnTime xCloseTime, "SaveWork1", , True End Sub

Step 2

Now click on "Insert," select "Module," and type "Program 2" into the text box as shown in the below image.

Example

Sub SaveWork1() 'Updated by Nirmal Application.DisplayAlerts = False ActiveWorkbook.Save ActiveWorkbook.Close Application.DisplayAlerts = True End Sub

Step 2

Save the work sheet as a macro-enabled template, close the sheet, and then reopen the sheet. Then click on "Enable code," then enter the time you want your sheet to open, then click "OK."

Conclusion

In this tutorial, we used a simple example to demonstrate how you can automatically save and close an Excel workbook after a certain idle time.

Updated on: 11-Jan-2023

934 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements