Remove users from shared Workbook if workbook opened more than 2 hours-CodePudding

I need to remove users from shared Workbook if workbook opened more than 2 hours by that user.

The below code remove all users (Except me), How to adapt it to add condition to remove only users whom opened this work for more than 2 hours.

Sub Remove_Timed_Users_from_Shared_Workbook()
   
    Dim UsrList()
    UsrList = ThisWorkbook.UserStatus

    For i = UBound(UsrList, 1) To 1 Step -1
        If UsrList(i, 1) <> Application.UserName Then
            ThisWorkbook.RemoveUser i
        End If
    Next 
End Sub

CodePudding user response：

Check the 2nd argument of UserStatus for the timestamp, calculate how many seconds it has been since that timestamp, and then you can compare it to 2 hours with some math.

Sub Remove_Timed_Users_from_Shared_Workbook()
    Const TwoHours As Long = 60 * 60 * 2
    Dim Seconds As Long
    Dim i As Integer
    Dim UsrList()
    UsrList = ThisWorkbook.UserStatus

    For i = UBound(UsrList, 1) To 1 Step -1
        If UsrList(i, 1) <> Application.UserName Then
         'If UsrList(i, 3) = 2 Then ' if it is shared/removable(?)
            Seconds = DateDiff("s", UsrList(i, 2), Now)
            If Seconds > TwoHours Then ThisWorkbook.RemoveUser i
         'End If
        End If
    Next
End Sub

Not sure it will let you actually remove the user tho. Please let us know if there is some restriction you are not able to get around. If the Shared/Exclusive matters, you can use the check on the lines I commented out.

CodePudding user response：

I'm unable to test this since I'm not on a work network, but I think ThisWorkbook.UserStatus(2) is what you're looking for.

[Workbook.UserStatus] returns a 1-based, two-dimensional array that provides information about each user who has the workbook open as a shared list.

The first element of the second dimension is the name of the user, the second element is the date and time when the user last opened the workbook, and the third element is a number indicating the type of list (1 indicates exclusive, and 2 indicates shared).

However your plan seems dangerous. What happens if the user is in the middle of working on the workbook when the clock hits 2 hours? Kick 'em out anyway, potentially losing unsaved changes?