Software & Utilities
Excel Tip: Closing Excel File After Idle Time - Step by Step
Learn how to auto save and close excel file that has been idle for specific period of time.
Posted: Jun 2010
In case that you have Excel file on shared disk, you know that only one person at time can have Write authorization. All other users that access that file, while first user is still using the file, can have only Read authorization. This is normal situation.
But probably you faced situation, for many times, that some user opened the file and forget to close it. If there are multiple potential users, than this could be a real problem.
One solution is that you call every user until you find who left the file open and ask that particular user to close file. This is really annoying, especially if there are many users and if they the leave office.
The other solution is to use Microsoft Excel Macro that will auto save and close the excel file, after specific period of idle time. If you do not know how to use excel macro or Microsoft visual basic editor just follow step by step instruction:
Open the excel file and go to View/Macos, select This Workbook, type the name in the Macro name box ( no space between words ) and click Create. The Microsoft Visual Basic will open.
Click to ThisWorkbook in the Visual Basic editor and paste the following code:
Private Sub Workbook_Open()
Reset
End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Reset
End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Reset
End Sub
Click to Module1 and paste the following code. You can edit the time period after which the file will auto save and close. Just edit "00:00:20" which is default ( HH:MM:SS).
Sub Reset()
Static SchedSave
If SchedSave <> 0 Then
Application.OnTime SchedSave, "SaveWork", , False
End If
SchedSave = Now + TimeValue("00:00:20") ' 20 seconds
Application.OnTime SchedSave, "SaveWork", , True
End Sub
Sub SaveWork()
ThisWorkbook.Save
ThisWorkbook.Close
End Sub
Save the file.
That's it. Do not forget to enable macros on all computers, otherwise the macro will not work.
