Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

VBA Code To Determine Who Has File Open

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • VBA Code To Determine Who Has File Open

    Is there an object/property available in VBA that would show who has locked a file (the username in the "nnnnnn.xls is locked for editting by 'Username')?

  • #2
    Re: VBA Code To Determine Who Has File Open

    Hi jgrove,

    I'm using this code:


    Code:
    Private Sub Workbook_Open()
    If ActiveWorkbook.ReadOnly Then
    Dim Folder As String
    Dim FName As String
    
    Folder = "c:\files\"
    RowCount = 1
    FName = Dir(Folder & "filename.xlsm")
     MsgBox "The file is locked by " & GetFileOwner(Folder, FName) & "."
    Else
        ActiveWorkbook.Save
    End If
    End Sub
    
    
    Function GetFileOwner(fileDir As String, fileName As String) As String
    
    'On Error Resume Next
    Dim secUtil As Object
    Dim secDesc As Object
    Set secUtil = CreateObject("ADsSecurityUtility")
    Set secDesc = secUtil.GetSecurityDescriptor(fileDir & fileName, 1, 1)
    GetFileOwner = secDesc.owner
    End Function


    replace c:\files\ and filename.xlsm with the correct values.
    when you open the file and it is locked, open it as read only and a new popupbox will tell you the current user.
    when the file is not locked and you open it, it will save it directly (to add the owner info into the file)

    hopes it works for you.

    greetings,

    Five

    Comment


    • #3
      Re: VBA Code To Determine Who Has File Open

      Five, thanks for helping out and welcome Could you please use code tags when posting code, thanks.

      Comment


      • #4
        Re: VBA Code To Determine Who Has File Open

        Thanks Five, works well, but made a minor adjustment.

        Because in most places where I'm calling this from I only have the full filename (including path) available, it was more efficient to just pass the Filename as is ...


        Code:
        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 a lot for your help,

        Cheers, Jeff

        Comment


        • #5
          Re: VBA Code To Determine Who Has File Open

          This is a great step forward on a problem I have been unable to solve for years!

          However it seems to fail on network drives (we use a distributed file system) - when i try and find out just who exactly has the file I want open, it just gives me *my* Windows username back.

          Is there a version that works with a network?

          Comment


          • #6
            Re: VBA Code To Determine Who Has File Open

            A little bit of Google bashing lead me to:
            http://www.xcelfiles.com/IsFileOpen.html

            Two versions of code to try there. Do let us know the results.

            Comment


            • #7
              Re: VBA Code To Determine Who Has File Open

              Alas I've tried that and it only works for Excel 2003 files.
              2007 (and later) .xlsm and .xlsx files have a different format - OpenXML.
              Using the code on these files generates garbage output sadly.

              Comment

              Trending

              Collapse

              There are no results that meet this criteria.

              Working...
              X