none
Auto close opened excel files RRS feed

  • 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

    Friday, March 27, 2015 6:53 AM

Answers

  • 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


    Friday, March 27, 2015 12:07 PM
  • 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!

    Friday, March 27, 2015 12:11 PM
    Moderator
  • 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.

    Monday, March 30, 2015 10:36 AM
    Moderator

All replies

  • 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


    Friday, March 27, 2015 12:07 PM
  • 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!

    Friday, March 27, 2015 12:11 PM
    Moderator
  • 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?

    Monday, March 30, 2015 7:51 AM
  • 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.

    Monday, March 30, 2015 10:36 AM
    Moderator
  • 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

    Tuesday, April 7, 2015 11:36 AM
  • 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)
    Tuesday, April 7, 2015 11:42 AM
    Moderator