Skip to: Site menu | Main content

Login

Name: 
Password:
Remember me?
Register

Get the name of the user who currently has the workbook open

written by Helen Toomik - Last updated Oct 2004

If a workbook is saved on a network drive, several users may try to open it at the same time. If you do this manually, a warning message will come up, and it will also tell you who has the file open. How can we get the same information when opening a workbook with code? There's no official documented solution for this, but trial and error and lateral thinking have led to a workable solution.

Is the workbook open?

The first step is to find out whether the file is open. We can try to open the file with Read Lock - if that fails the file is already in use elsewhere.

Public Function IsFileLocked(strFileName As String) As Boolean 
     
    On Error Resume Next 
    Dim FF As Integer 
    FF = FreeFile 
     'An error occurs if the document is currently open.
    Open strFileName For Binary Access Read Lock Read As #FF 
    Close #FF 
     
     'Check for Error
    If Err.Number Then 
        Err.Clear 
        IsFileLocked = True 
    End If 
     
End Function 
 
 

Who has it open?

The second step is to find out who has the file open. Excel stores the name of the person who last accessed the workbook, in the workbook file itself. For an open workbook you can see who has it open; for a closed workbook you can see who last opened it.

Again, we open the workbook as a text file. The user name appears to be stored as plain text, not encrypted in any way. It seems to be surrounded by special characters that we can use to locate it - Chr(0) before it, and two Chr(32) after it. Chr(0) occurs in other places in the file as well, but the first occurrence of two Chr(32) appears to always come just after the user name. So we search for two Chr(32), and then search backwards from that point until we find Chr(0). In Excel 2000 and above we can use InStrRev for the backwards search; in Excel97 that will have to be replaced with a loop.

There's a slight complication. When Excel overwrites a username with a shorter one, it doesn't always adjust the length of the string. If, for example, "jsmith" uses the file first, after which "jdoe" opens the file, jdoe is written over jsmith so the string between the Chr(0) and Chr(32) will be "jdoeth". However, the real length of the latest username is stored in the character just preceding the two Chr(0)s - the ASCII code of that character gives the length of the string. We can use that to get rid of the leftovers from the previous username, and extract the relevant part of the string only.

To summarise, here's the whole procedure. First look for two Chr(32)s; those will come closely after the end of the username. Then look back to find two Chr(0)s that mark the beginning of the username. Then look at the character just before the Chr(0)s to get the exact length of the username.

Here's some sample code, using InStrRev for the reverse search:

Function LastUser(strFileName As String) As String 
     
    Dim strWholeFile As String 
    Dim strFlag1 As String, strFlag2 As String 
    Dim intNameLength As Integer 
    Dim i As Integer, j As Integer 
     
    strFlag1 = Chr(0) & Chr(0) 
    strFlag2 = Chr(32) & Chr(32) 
     
    Open strFilename For Binary As #1 
    strWholeFile = Space(LOF(1)) 
    Get 1, , strWholeFile 
    Close #1 
     
    j = InStr(1, strWholeFile, strFlag2) 
    i = InStrRev(strWholeFile, strFlag1, j) + Len(strFlag1) 
    intNameLength = Asc(Mid(strWholeFile, i - 3, 1)) 
    LastUser = Mid(strWholeFile, i, intNameLength) 
     
End Function 

That's it!

Combine these two functions, and you can find out whether the file is accessible, and if not, who has it open.

The ideas for these techniques came from member Wamphyri at www.xtremevbtalk.com, and Mark Rowlinson. Thanks to Ivan F Moala for solving the problem with leftovers from previous names.