Get username if file already open

hstaubyn

Board Regular
Joined
Sep 13, 2010
Messages
93
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>
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Code:
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
 
Upvote 0
You can use:

Code:
Environ$("USERNAME")

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

Code:
 username = Environ$("USERNAME")
 
Upvote 0
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...
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
Can you test this:
Code:
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
 
Upvote 0
I get the following error message:

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

Debugger takes me to the line:

Code:
Set secDesc = secUtil.GetSecurityDescriptor(strfileName, 1, 1)
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top