Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How do you determine who has a file open (97)

    I have an addin that operates the same aas the work menu in Word.
    It allows me to open files quickly and easily without the bothersome network naviagtion.

    It works fine, however it does not alert me to the fact that I am opening a readonly copy of the file before it opens. (the only way to tell is the read-only postscript added to the workbook name)
    Is there a way I can :
    1. Alert me when the file is opned that the file is being used by another, (so I can chose to open, notify later, cancel)and
    2. Determine after opening it who is the other user (so I can track the user down to close the file so I can edit it)


    Thanks in advance for any ideas

  2. #2
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do you determine who has a file open (97)

    you could create a log when the work book is opened and closed. your add in could then test for if the workbook is read only and if so open the log file. I have included a workbook that has some code you can play with. have a meeting i have to get to and so dont have time at this moment to go into greater detail.
    Attached Files Attached Files

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: How do you determine who has a file open (97)

    Hi,
    There is some code in <!post=this post,158682>this post<!/post> that will alert you that the file is already open. I will see if I can find something to establish who the other user is.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do you determine who has a file open (97)

    Thanks,
    That is a great start and works well.

    Please let me know if you have any luck in identifying the current user.
    It must be 'in there' somewhere as the standard file open dialog will tell you the user, but I cannot find a reference to that property anywhere.

  5. #5
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do you determine who has a file open (97)

    Just some rambling thoughts:

    If I open a file on the network, then open - in a new instance of excel - the same file, I get the file in use dialog displayed. It tells me that the file is locked for editing by 'Company X'. I think this depends on the user name in Tools:OPtions:General, so that if your IT department has the policy, as our does, of using a generic name in the office installation, what you end up achieving here may not give you precisely the results you're after. So I believe you're going to have to go after the network username.

    Whether there's a way to get the network username, I'm not sure. I've had a quick look at API calls but can't see anything. I seem to vaguely remember a thread about this in the VB/VBA forum, though what I've found so far doesn't come up with the complete goods. However, in addition to Rory's link, here are some related threads to give some broad reading material around the subject:

    Determine where File Is Open

    User Logged in? (All)

    Just another thought and that's my limit for the day: would it be worth moving this thread to VB/VBA so that the likes of Mr Greaves and Kevin get to see it?

  6. #6
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do you determine who has a file open (97)

    Thanks Brooke,

    I am (painfully) aware of the Tools:Options:General pitfalls where the default installation sets up the user name, but most users have entered their correct name by now.

    Just as a rambling thought too; when an excel file is opened the data/time stamp is updated (even if it is not changed), and I am supposing that excel is storing the current user and open status in the file itself. Could there be a method to extract this from the file? (I think that if there are no modifications to the file and it is closed that the date/time reverts to original! - <strange?>)
    I remember that somewhere in the lounge there was a thread on looking up properties in files without opening them. I will see if I can find it.

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do you determine who has a file open (97)

    Try this. The section you want is about 3/4 of the page down.

  8. #8
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do you determine who has a file open (97)

    Thanks,

    It is exactly what I want.

    I feel a bit silly really as I usually check Chip first, but must have had a bad week.

    My work around in the interim was to not use a work menu, but create a spreadsheet with hyperlinks to all of the files. (Could have done it as a HTML page too, but Excel 97 does not support save as HTML)
    Opening a file in this method then invokes the usual Excel messages etc, allows other document types to be opened, and folder views - and had the added advantage that the rest of my colleagues can now find all of the files faster too.

Posting Permissions

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