2

I'm looking for some code to tell me the username of the user that has an Excel (.xls) file open. Currently, the code will throw up a msgbox if the file opens as read only, but I also want it to tell me who has it open. It gives the name when opening manually as a "locked for editing" error message. Is this a possible function to add?

I've researched many websites but have not found one suitable, or just had no answers at all.

I have also looked into reading the locked file that gets created, but still cannot get it to work.

xlApp = New Microsoft.Office.Interop.Excel.ApplicationClass
xlWorkBook = xlApp.Workbooks.Open("\\********", IO.FileAccess.Write)
If xlWorkBook.ReadOnly = True Then
    xlWorkBook.Close(SaveChanges:=False)
    xlApp.Quit()
    releaseObject(xlApp)
    releaseObject(xlWorkBook)
    MsgBox("Workbook Open")
Else
    xlApp.Visible = True
End If

Where the msgbox is, I would like it to show the username of who has it open.

Andrew Morton
  • 24,203
  • 9
  • 60
  • 84
Hulk Smash 93
  • 55
  • 1
  • 8

1 Answers1

1

Excel creates a hidden file in the same directory as the document with the filename prepended by "~$". The owner of that file should be the account which opened the Excel document (as pointed out by contributor Philip Hanebeck in Find out who has Excel file locked for editing).

Putting the two together:

Dim excelFile = "C:\temp\mydoc.xlsx"
Dim lockFile = Path.Combine(Path.GetDirectoryName(excelFile), "~$" & Path.GetFileName(excelFile))
Dim fileOwner = File.GetAccessControl(lockFile).GetOwner(GetType(Security.Principal.NTAccount)).ToString()
MsgBox(fileOwner)

I only have me on my computer to test it with: you will need to check it more rigorously and put in error handling.

Note: in the article I linked to earlier, it mentions that this method may not work for .xls file, but it doesn't say if that is using an earlier version of Excel. And it might not work where the filepath starts with "\\" rather than on a mapped drive.

Additional material: Find out File Owner/Creator in C#.

Andrew Morton
  • 24,203
  • 9
  • 60
  • 84
  • `File.GetAccessControl(lockFile)`, if the file is `ReadOnly` which more than likely it will be, that code will throw a `UnauthorizedAccessException`, I would check this first before trying to call `GetOwner`... – Trevor Mar 26 '19 at 19:19
  • @Çöđěxěŕ So is it not possible to get the owner of a read-only file with .NET Framework methods? Although I suspect the ~$ file will not be read-only. – Andrew Morton Mar 26 '19 at 19:36
  • good question. according to [this](https://learn.microsoft.com/en-us/dotnet/api/system.io.file.getaccesscontrol?view=netframework-4.7.2) a `UnauthorizedAccessException` can occur when the file is `ReadOnly`. It's possible the file could be in `ReadOnly`, if so that exception would occur. IMHO, it would be best to wrap the call to `File.GetAccessControl(lockFile)` before trying to access anything else from that object; it may be null/nothing ... – Trevor Mar 27 '19 at 12:55
  • This is great, thank you. But it comes up with as a userid rather than a user name. I tried to translate it to windowsidentity, but its not working for me – Hulk Smash 93 Mar 28 '19 at 09:48
  • Is that a userid in the form of "S-1-5-32-545"? – Andrew Morton Mar 28 '19 at 10:05
  • No, its in the form of "domain\Userid" I can change it to show the SID, but again, i cant see where to change it to afterwards – Hulk Smash 93 Mar 28 '19 at 13:48
  • @HulkSmash93 Does Excel itself show the username entered in "File" -> "Options" -> "General" -> (Personalize your copy of Microsoft Office) "User name:"? If so, and that's the information you need, this method won't work to get that, and you would have to figure out how to parse the ~$ file. – Andrew Morton Mar 28 '19 at 13:54
  • ah ok, I will look into it. Thank you for your help so far with this though. – Hulk Smash 93 Mar 28 '19 at 14:18