Excel 2010 - Who is locking the file?

Hi,

I have been researching this for a while but I cannot seem to identify the relevant information.

I have just upgraded from Excel 2007 to Excel 2010. Several users will be accessing a particular spreadsheet under XL10. (This is not configured as a shared workbook due to the continuing limitations and problems attached to workbook sharing in client Excel. Ditto limitations under Excel Web App but I digress.)

Under XL07, if the workbook is being currently edited/locked by a user, and another user attempts to open, a message is displayed stating who has the workbook locked, whether to notify, cancel etc.

This message is absent in XL2010: if the workbook is locked, it opens by default in read-only mode for 2nd and subsequent users, with the only indication that you are in read-only mode being [Read-Only] appended to the workbook name in the title bar. My concern is that this indication may be missed, and the user will proceed to make changes that cannot be saved to the original file when done.

Firstly, how can one determine thro' VBA that one is accessing the file in R/O mode and thereby display a more prominent message to the R/O user by way of message box or otherwise?

Secondly, how can one determine through VBA the identity of the user who currently has write access to the file ? Given that this information was conveniently conveyed to the user in XL07 in the form of the message as mentioned (but then dropped in XL10) surely there must be a way of accessing this information in VBA?

I have looked at Environ("Username") and application.username, but these do not appear to be relevant (they do not provide the identity of the user with current write access, they merely provide info related to the currently logged on user). I have also looked at .writereserved and .writereservedby to see if a file is locked and by whom respectively but the former is returning false even when I know the file is locked.

Any help greatly appreciated.

Rgds Orson.

Ok, so my first question is pretty easily sorted by checking activeworkbook.readonly status.

Please anybody got any advice on how I can find out who has write access to the file?

Orson.

Dominic,

Thanks for the references. However, due to a particularly bad episode of brain freeze on my part, this is all redundant.

In addition to migrating from XL07 to XL10 I am also porting to a new server environment and guess what.. I forgot to set write permissions for the users So my belief that the "this file is locked for editing by [etc]" message had been dropped in XL10 is completely wrong, File access was defaulting straight to R/O mode by virtue of the permissions at server level only. The message is still there.

Sorry all for this confusion.

By the time I had realised this, I had already cobbled together a wheel re-invention from various sources as below, which might be useful if one wishes to re-display the current (or more correctly, last-known) write-access user details during an active Excel session or something I don't know...

Sorry again for the incorrect steer and thanks for the advice.

Regards
Orson.

Please Login or Register  to view this content.