|
|
In
case that you have Excel file on shared disk, you
know that only one person at same time can have Write
authorization. All other users that access the file
after the first user 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 these
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
file
That's
it. Do not forget to enable macros on all computers,
otherwise the macro will not work.
|
