Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Get username if file already open

This is a discussion on Get username if file already open within the Excel Questions forums, part of the Question Forums category; Hi, I have a bit of code that detects whether or not an excel file is already open - it ...

  1. #1
    Board Regular
    Join Date
    Sep 2010
    Posts
    89

    Default Get username if file already open

    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- is locked for editing, by -username-. Open read-only or click notify to (etc.)"

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

    Does anyone know how this can be done?

  2. #2
    Board Regular
    Join Date
    Jul 2012
    Location
    Livorno, Italy
    Posts
    1,950

    Default Re: Get username if file already open

    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

  3. #3
    Board Regular Jeffrey Lopez's Avatar
    Join Date
    Jul 2013
    Location
    Austin, Texas
    Posts
    479

    Default Re: Get username if file already open

    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")
    As always, please be sure to back up any workbooks that I have suggested a solution for.

    If you want to touch the past, touch a rock. If you want to touch the present, touch a flower. If you want to touch the future, touch a life.

  4. #4
    Board Regular
    Join Date
    Sep 2010
    Posts
    89

    Default Re: Get username if file already open

    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...

  5. #5
    Board Regular Jeffrey Lopez's Avatar
    Join Date
    Jul 2013
    Location
    Austin, Texas
    Posts
    479

    Default Re: Get username if file already open

    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?
    As always, please be sure to back up any workbooks that I have suggested a solution for.

    If you want to touch the past, touch a rock. If you want to touch the present, touch a flower. If you want to touch the future, touch a life.

  6. #6
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    East Sussex
    Posts
    22,253

    Default Re: Get username if file already open

    See if the code here works for you: VBA How to report which User has File Open?

  7. #7
    Board Regular
    Join Date
    Sep 2010
    Posts
    89

    Default Re: Get username if file already open

    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 (VBA How to report which User has File Open?)

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

  8. #8
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    East Sussex
    Posts
    22,253

    Default Re: Get username if file already open

    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.

  9. #9
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    East Sussex
    Posts
    22,253

    Default Re: Get username if file already open

    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

  10. #10
    Board Regular
    Join Date
    Sep 2010
    Posts
    89

    Default Re: Get username if file already open

    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)

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com