Get the name of the user who currently has the workbook open
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.
