BIZ SPONZORS
popular biz reading
Marketing Mix: Promotion
Employee Turnover
Marketing Mix: Price
Planning and Organizing
Maslow's Hierarchy of Needs
Key Performance Indicators
Sustainable Competitive Advantage
Ishikawa Fishbone Diagram
Price Determination
Supply Chain Concept
Employee Induction
3 Basic Finance Statements
Sales Forecast Accuracy
FMCG Sales Boosting
OTIF - On Time In Full
Merchandising at the Point of Sales
Promotion Mix: Advertising
Employee Motivation
Porter's 5 Forces
CHECK ON BIZ DEVELOPMENT
biz sponsors
BrainCast Relaxation
Advertise on Biz Development
My BrainCast
Energy Booster
Twitter
biz archive
2012
2011
2010
2009
2008
2007

My Introspective

by Laurus Nobilis
My BrainCast

Software & Utilities

Excel Tip: Closing Excel File After Idle Time - Step by Step

 

Excel

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.

Do you have shared Excel file that is accessed by different users and you are tired of checking who left the file opened? The solution is simple.



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.

Closing Excel After Idle Time - Step 1

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

Closing Excel After Idle Time - Step 1

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

Closing Excel After Idle Time - Step 1

Save the file.

That's it. Do not forget to enable macros on all computers, otherwise the macro will not work.

Download - Close Excel After Idle Time

 

 

 

My BrainCast Self Improvement
blog comments powered by Disqus
 
     
My BrainCast My BrainCast energy Booster My Braincast Deep Sleep
My BrainCast Mandala Meditation My BrainCast Relaxation
my-braincast