Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 7 of 7

Thread: VBA Code To Determine Who Has File Open

  1. #1
    Join Date
    25th June 2010
    Posts
    5

    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')?

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    4th March 2010
    Posts
    4

    Re: VBA Code To Determine Who Has File Open

    Hi jgrove,

    I'm using this code:


    VB:
    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

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,709

    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.

  4. #4
    Join Date
    25th June 2010
    Posts
    5

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


    VB:
    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

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    16th September 2010
    Posts
    2

    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?

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    9th September 2009
    Posts
    1,599

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    16th September 2010
    Posts
    2

    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.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Publisher 2007 - cannot open any PUB file - open file error code 1100009 with Office SP2
    By Dave Hawley in forum Office 2007 Migration and Application Compatibility
    Replies: 0
    Last Post: May 5th, 2009, 23:20
  2. Open File Code
    By Dennisli2000 in forum EXCEL HELP
    Replies: 3
    Last Post: September 12th, 2007, 02:46
  3. Code To Open Html File
    By aturnon in forum EXCEL HELP
    Replies: 2
    Last Post: April 24th, 2007, 05:03
  4. code to open file
    By msn_manju in forum EXCEL HELP
    Replies: 2
    Last Post: August 15th, 2005, 15:25
  5. Code to Open File with “Wildcard”
    By orion in forum EXCEL HELP
    Replies: 4
    Last Post: June 13th, 2003, 18:30

Bookmarks

Posting Permissions

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