Get username if file already open

[hstaubyn]

Hi,

I have a bit of code that detects whether or not an excel file is already open - it returns a value 'true' or 'false' and then the rest of the code either executes or stops depending on the answer. This is great as it means that changes can be made to a master document by the code and saved without fear of being lost.

What I would like it to also do is, in the case that the file is already open by someone else on the network, to display the username of the person who has it open.

When you try to manually open an already open file, a dialogue box pops up saying "-Filename-<filename> is locked for editing, by <username>-username-. Open read-only or click notify to (etc.)"

How do you get VBA code to read <username>-username-?

Does anyone know how this can be done?</username></username></filename>

 

[patel45]

Sub GetTheNameAPP()
     '   Deliver the name via msgbox
     '   Not reliable, returns the office username, not Windows
    MsgBox "Application username is: " & Application.UserName
End Sub
 
 
Sub GetTheNameENVIRON()
     '   Deliver the name via msgbox
     '   Same as the api version,
    MsgBox "Environ username is: " & Environ("USERNAME")
End Sub

 

[Jeffrey Lopez]

You can use:

Environ$("USERNAME")

This will pull the user name, so you could assign it to a variable if needed:

 username = Environ$("USERNAME")

 

[hstaubyn]

Thanks for the reply,

that only returns my own username, what I need it to do is return the username of the person on the network who has the file open...

 

[Jeffrey Lopez]

Ah, I see what you are saying, and yeah, because you are the one that has it open. Someone may know better than me, but I don't think that is doable... but interested in seeing if there is! Although not sure why that it relevant, since you can't have two people in the same workbook, are you not getting the "Read Only/Notify" prompts when someone has the file open?

 

[RoryA]

See if the code here works for you: http://www.mrexcel.com/forum/excel-...plications-how-report-user-has-file-open.html

 

[hstaubyn]

Hi Rory,

thanks for the reply, I tried the code in that post, but it didn't work. I replied on that post by mistake (http://www.mrexcel.com/forum/excel-...plications-how-report-user-has-file-open.html)

Do you know why I would get a run time error at that point?

 

[RoryA]

I think that code probably only works with xls files and not xlsx (or other XML files, which are essentially zipped folders). I'll see what I can dig up.

 

[RoryA]

Can you test this:

Function GetFileOwner(strfileName As String) As String
 
    Dim secUtil As Object
    Dim secDesc As Object
 
    Set secUtil = CreateObject("ADsSecurityUtility")
    Set secDesc = secUtil.GetSecurityDescriptor(strfilename, 1, 1)
    GetFileOwner = secDesc.Owner
 
End Function

 

[hstaubyn]

I get the following error message:

Run-time error '5':
Invalid procedure call or argument

Debugger takes me to the line:

Set secDesc = secUtil.GetSecurityDescriptor(strfileName, 1, 1)