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

[kieran]

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

[notbrl]

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.

[rory]

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.

[kieran]

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.

[Brooke]

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?

[kieran]

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.

[jstevens]

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

[kieran]

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.