Kicking User out of Document

[davidhall80]

I've created a folder and spreadsheet on a shared drive. Since I am the Author of the document, Can I kick a user out of the document if I wish. Thanks in advance

 

[Oorang]

Hi David,
This is an advanced/intermediate request. You cannot (with word) kick a user out of a document. You can with vba, but there are a few considerations. First, is the issue of lost work. You can make it auto-save but then you might end up with saved changes you don't want. You can make it prompt the user, but if the user is away from their desk, then the prompt will stay up forever and they will never be booted. It is possible to create prompts that time out, but the wShell.Popup method's timeout doesn't work properly from vba so you end up having to "roll your own" which is, of course, scope creep.
It is generally preferable to manage this issue by either sharing the Workbook, or creating a password for editing, so unless you have the password you be in read-only mode. All that said, here is how to do what you asked:

'Must be in "ThisWorkBook" module:
Private Sub Workbook_Open()
    KickCatcher
End Sub

'Place in standard module:
Option Explicit

Private Enum abBootType
    'To use these, add. Example boot persistant with no warning = 5
    BootNo = 0
    BootOnce = 1
    BootPersistant = 2
    BootYes = 3
    NoWarning = 4
End Enum

Public Sub KickCatcher()
    Dim strBootFile As String
    Dim blnSaveChanges As Boolean
    Dim eBootType As abBootType
    If ThisWorkbook.ReadOnly Then Exit Sub
    DoEvents
    Application.OnTime DateAdd("s", 1, Now), "KickCatcher"
    'Get boot file name:
    strBootFile = ThisWorkbook.FullName
    strBootFile = Left$(strBootFile, InStrRev(strBootFile, ".")) & "dat"
    If LenB(Dir(strBootFile)) Then
        eBootType = Val(GetFileText(strBootFile))
        If (eBootType And BootYes) <> BootNo Then
            If (eBootType And NoWarning) <> NoWarning Then
                blnSaveChanges = MsgBox("The author of this document has booted you." & vbNewLine & vbNewLine & "Do you want to save your work?", "Administrative Action", vbQuestion + vbYesNo + vbDefaultButton1) = vbYes
            End If
            If (eBootType And BootOnce) Then
                Kill strBootFile
                CreateEmptyFile strBootFile
            End If
            Exit Sub
            ThisDocument.Close blnSaveChanges
        End If
    End If
End Sub

Private Function GetFileText(ByVal path As String) As String
    Dim lngFileNum As Long
    Dim strRtnVal As String
    lngFileNum = FreeFile
    Open path For Binary Access Read Shared As #lngFileNum
    strRtnVal = String$(FileLen(path), vbNullChar)
    Get #lngFileNum, , strRtnVal
    Close #lngFileNum
    GetFileText = strRtnVal
End Function

Private Sub CreateEmptyFile(ByVal path As String)
    Dim lngFileNum As Long
    lngFileNum = FreeFile
    Open path For Binary Access Write As #lngFileNum
    Close #lngFileNum
End Sub

How to use:
After you have placed the code in the workbook you want, create a text file in the same folder as the workbook, with the same name as the workbook (ex: "C:\test\book1.txt"). Rename it to the ".dat" extenstion ("book1.dat"). When you are ready to boot some one put the number of the boot method in dat file (you can just open it with notepad to edit). The numbers to use are as follows:

1.) Boot one time only, prompts users for save.
2.) Always boot users until you remove the 2 from the file (prompts for save).
5.) Same as 1 no prompt for save.
6.) Same as 2 no prompt for save.

 

[davidhall80]

Thanks you very much Oorang. I am still studying this code before I run with it.... I'll post results shortly. Thanks again.

 

[davidhall80]

Where do I put the String of the file that I am trying to kick someone out of??

 

[Oorang]

I'm not sure I understand your question?

 

[davidhall80]

sorry bout that....makes sense, I got it to work

 

[ajm]

oorang, i am trying to use your code below but get an error with "ThisDocument.Close blnSaveChanges" from the Public Sub KickCatcher() macro. the error message is that ThisDocument is an undeclared variable. should it be ThisWorkbook instead? I am using Excel 2003.

 

[Richard Schollar]

Hi

I'm pretty sure that that is meant to be ThisWorkbook - give it a try and see if it works

 

[Oorang]

Richard is right. The "ThisDocument" syntax was for word. I must not have missed that last one. Sorry

 

[ajm]

further questions. is this code for a shared workbook or will it also work when there is someone in your workbook and you open a read only version? also, with the .dat file; should it be empty until you need it? by this i mean, you should only put a value into it when you need to kick someone out? after you have kicked them out, do you then delete your selected option number, and save it as an empty file? and how does your workbook know that you have opened the .dat file and inserted a number?

Sorry if my questions seem a little silly, i don't really understand how the code does what it does and in what order i should be doing things. I could follow your instructions on where to place the pieces of code but am not too clear on the operation of it.