Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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.
1K+ Views
