How to find the user who opened excel file file from vba

[tdesilva]

How to determine the user who is locking the excel file from vba.

I can find the owner of the excel file but owner is not always the last user

Private Sub clientList_Click()
Dim FileNm As String

Dim ret
FileNm = "C:\client list.xlsm"

ret = IsWorkBookOpen(FileNm)
If ret = True Then

'MsgBox "File is opened by " & GetFileOwner(FileNm) & "."
Else
MsgBox "File is Closed"
End If
End sub

Function IsWorkBookOpen(fileName As String)
Dim ff As Long, ErrNo As Long
On Error Resume Next
ff = FreeFile()
Open fileName For Input Lock Read As #ff
Close ff
ErrNo = Err
On Error GoTo 0
Select Case ErrNo
Case 0: IsWorkBookOpen = False
Case 70: IsWorkBookOpen = True
Case Else: Error ErrNo
End Select
End Function
Function GetFileOwner(fileName As String) As String
Dim secUtil As Object
Dim secDesc As Object
Dim File_Shortname As String
Dim fileDir As String
File_Shortname = Dir(fileName)
fileDir = Left(fileName, InStr(1, fileName, File_Shortname) - 1)
Set secUtil = CreateObject("ADsSecurityUtility")
Set secDesc = secUtil.GetSecurityDescriptor(fileDir & File_Shortname, 1, 1)
GetFileOwner = secDesc.owner
End Function

Thanks, if you can let me how to find the user who is locking the excel file

 

[Logit]

.
This resource seems to have some good answers : windows - Find out who is locking a file on a network share - Stack Overflow

I can give you a link to two files that track changes. They would show who opened the file, date and time if that would help.


I've never worked with networks so I'm hoping the above URL is on target for your issue.