How to automatically save and close an Excel file after a certain idle time?
In some cases, such as leaving an Excel file on a shared disk, when the file is still opening by a user, others are unable to edit the workbook. So how to auto close the file after a certain idle time in order to ease other’s work? This article will help you with VBA method.
Auto save and close an Excel file after a certain idle time with VBA code
Auto save and close an Excel file after a certain idle time with VBA code
To auto save and close an Excel file after a certain idle time, please do as follows.
1. Open the workbook you need to make it auto saved and closed after a certain idle time. Then press the Alt + F11 keys together to open the Microsoft Visual Basic for Applications window.
2. In the Microsoft Visual Basic for Applications window, please double click the ThisWorkbook in the right pane to open the Code window. Then copy and paste below VBA code into the Code window. See screenshot:
VBA code 1: Auto save and close an Excel file after a certain idle time
Dim xTime As String
Dim xWB As Workbook
Private Sub Workbook_Open()
'Updated by Extendoffice 2019/1/20
On Error Resume Next
xTime = Application.InputBox("Please specify the idle time:", "KuTool For Excel", "00:00:20", , , , , 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
3. Then go on clicking Insert > module, and copy and paste below code into the Module window. See screenshot:
VBA code 2: Auto save and close an Excel file after a certain idle time
Sub SaveWork1()
'Updated by Extendoffice 2019/1/20
Application.DisplayAlerts = False
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.DisplayAlerts = True
End Sub4. Press the Alt + Q keys simultaneously to close the Microsoft Visual Basic for Applications window.
5. Click File > Save As > Browse. See screenshot:
6. In the Save As dialog box, please select a folder to save the file, name it as you need in the File name box, select Excel Macro-Enabled Workbook from the Save as type drop-down list, and finally click the Save button. See screenshot:
From now on, every time when opening this workbook, a Kutools for Excel dialog box will pop up. Please enter the time you will save and close the workbook based on, and then click the OK button.
And the workbook will be saved and closed automatically after that specified idle time. See screenshot:
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
