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

    Edited once, last by royUK: remove indent tags ().

  • Re: VBA Code To Determine Who Has File Open


    Hi jgrove,


    I'm using this code:






    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

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





    Thanks a lot for your help,

    Cheers, Jeff

  • 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?

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