Auto close opened excel files

Question

Hello Guys,

We need help with excel. There are many users who forgot to close excel and open files and when someone else tries to open file he gets error message. Is there a way to configure so that excel shows who is user and if i can configure auto close after example 5 min.

Thank you in advance

Answer 1

Hello Guys,

We need help with excel. There are many users who forgot to close excel and open files and when someone else tries to open file he gets error message. Is there a way to configure so that excel shows who is user and if i can configure auto close after example 5 min.

Thank you in advance

The following Sub goes into the Workbook

Private Sub Workbook_Open()
    EndTime = Now + TimeValue("00:05:00")
    RunTime
End Sub

This one goes in a separate module:

Public EndTime
Sub RunTime()
    Application.OnTime _
    EarliestTime:=EndTime, _
    Procedure:="CloseWB", _
    Schedule:=True
End Sub
 
Sub CloseWB()
    Application.DisplayAlerts = False
    With ThisWorkbook
        .Saved = True
        .Close
    End With
End Sub

The next one should go in each sheet you have in the WB

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If EndTime Then
        Application.OnTime _
        EarliestTime:=EndTime, _
        Procedure:="CloseWB", _
        Schedule:=False
        EndTime = Empty
    End If
    EndTime = Now + TimeValue("00:05:00")
    RunTime
End Sub

Answer 2

I assume by error message you mean the read-only prompt appears, though if opening with code you can prevent that. If you have opened the file as read-only you can check the workbook's WriteReservedBy property to return the username of person who has the file open.

It would be difficult to programmatically close the file without gaining access to the the user's system and Excel instance. However it would be possible to include an macro in the file to trigger 5 minutes after it opened and close itself. It might irritate your users though!

Answer 3

Hello Pete,

Problem is that is one user open file other users can't access it. They get error message that is used by someone else but not showing the user name. Is it possible to configure so that if someone forget to close the file and another person tries to access it shows who has the file opened?

Answer 4

Normally if a file is already open it is possible to open a second time after accepting the prompt that the file is read only, with an option to be informed if and when the file becomes fully available. How is the file being opened, manually or with code?

Have you looked at John's idea. It is along the lines of what I suggested but the close in 5 minutes time is reset after every time the user selects a different cell. That approach could be extended to other actions. Another idea, if the file hasn't been touched for say 5 minutes show a prompt to the user asking if he want's to keep the file open, if the user ignores the prompt close the file. This would need to be with a userform rather than a msgbox.

As I mentioned, to force the file to close would require access to the first user's system from the second user's system, eg perhaps run a VBScript. In most scenarios that would be difficult.

Have you looked into "Sharing" the file.

Answer 5

Hi Peter,

This file is in a shared folder. I can't share only this file if it is in shared folder. I will try with macro and see if it is working. Thank you all for helping me

Answer 6

I assumed the file was in a shared folder but I meant look at Review, Changes, Protect and Share Workbook (ie in Excel's Ribbon)