Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Who Has the File Open?.

Who Has the File Open?

by Allen Wyatt
(last updated June 23, 2018)

3

Joe asked if there is a way to determine, in a macro, who has a particular workbook open. He thought about using the WriteReservedBy property, but it doesn't seem to have the information he needs.

Unfortunately, there is no way to determine this information from VBA—it just isn't accessible. The WriteReservedBy property doesn't show who has a file open; it shows who saved the workbook using a password. In other words, when someone saves a workbook with the option to have a password to modify it, the file is "WriteReserved." The WriteReservedBy property contains the name of the person that saved the file in the WriteReserved state.

If you only need to know the answer (about who has the file open) periodically, it is easiest to gather a list of the open file names, and ask the network admin to tell you who has them open—such information is maintained on the network and accessible to the admin.

Another potential solution is to add an AutoOpen macro to each workbook that writes a temporary file to disk that contains the name of the person opening the file. The macro would need to not only open the temporary file, but handle error conditions, such as a temporary file that is already open. The temporary file could then be accessed by other macros to see the name that it contains.

An additional place that may hold an answer is the VBNet site. The article at this page contains code that may be adaptable for the desired information:

http://vbnet.mvps.org/index.html?code/network/netfileenum.htm

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2492) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Who Has the File Open?.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Starting the Equation Editor

The Equation Editor is a tool you can use to create complex mathematical formulas and insert them in your documents. ...

Discover More

Unwanted Vertical Lines in a Table

When you print a table that includes borders, those borders should be crisp and clear on the printout. If you get some ...

Discover More

Discovering Printer Drift

How accurate is your printer when it comes to placing information on the printed page? The simple technique described in ...

Discover More

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

More ExcelTips (menu)

Seeing Full File Names in the Files Menu

Wouldn't it be great if you could look at the files in the MRU list and see the full path and file names? Excel condenses ...

Discover More

Jumping Around Folders

When you open a workbook in Excel, the Open dialog box always starts within the folder in which you were last working. ...

Discover More

Full Path Names in Excel

Need to know what the full path name is for the current workbook? With a simple macro you can display the full path name ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is nine more than 0?

2016-12-01 11:54:05

LinearD

Working for a large company, we have many people jumping into a file at any given time. This became frustrating when I was trying update the workbook connections and not being able to save it. So I wrote two macros; one to log the users details to a text file using ENVIRON$(), and another that loops every minute or so and checks for a file called "force-close.txt" that was stored in another location. I just create the file with the same name when I need to and it forces the user to save a copy and close the file. A little inefficiant, but does the job.


2015-01-27 12:20:29

Wyatt S

I have written a macro that tells you who is in an XLSX file. I wrote a post about it here: https://bizintelsolutions.wordpress.com/2015/01/26/who-is-in-excel-xlsx-file/

-Wyatt


2014-10-16 11:06:18

shantaram


Hi,

How do we know that if anyone else open my excel sheet & close it without saving.

so how do we find out this person ?


This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.