Who has an excel file open?

Posted on 2011-10-13
Last Modified: 2012-05-12
Hi Experts, we have a mixed OS network (Windows 7 and XP) who share an excel spreadsheet on an SBS 2011 server. On occasions someone will open the file then go off for lunch so anyone else wanting to use the file gets the message "xxx.xls is locked for editing by another user". Is there any way that we can have the users run a script from their desktops to find out who has this particular file open?
Question by:leehewson
    LVL 4

    Accepted Solution

    there's an easier way to sort this out.

    just mark the files which are used by multiple users as "SHARED WORKBOOK"

    that way multiple users will be able to have it opened in the same time. It will also keep previous copies of the spreadsheet just in case someone overwrites someone else's data.

    Instructions on how to do this can be found here :

    LVL 10

    Expert Comment

    by:Arman Khodabande
    According to Microsoft (This article) :

    Word creates an owner file when you open a previously saved Word document. An owner file is temporary and holds the logon name of the person who opens the document.

    You just need to look at this owner file to understand!
    LVL 10

    Expert Comment

    by:Arman Khodabande
    Look at this article for more info. It says:

    Owner File (Same Directory as Source File)

    When a previously saved file is opened for editing, for printing, or for review, Word creates a temporary file that has a .doc file name extension. This file name extension begins with a tilde (~) that is followed by a dollar sign ($) that is followed by the remainder of the original file name. This temporary file holds the logon name of person who opens the file. This temporary file is called the "owner file."

    When you try to open a file that is available on a network and that is already opened by someone else, this file supplies the user name for the following error message:
    This file is already opened by user name. Would you like to make a copy of this file for your use?
    Word may be unable to create an owner file. For example, Word cannot create an owner file when the document is on a read-only share. In this case, the error message changes to the following error message:
    This file is already opened by another user. Would you like to make a copy of this file for your use?
    Note Word automatically deletes this temporary file from memory when the original file is closed.

    When you open a file by using the HTTP or FTP protocol, the file is first copied to the temp directory. Then, the file is opened from the temp directory.

    When you open a file on a UNC share with Word 2007, the file is first copied to the temp directory. Then, the file is opened from the temp directory.
    LVL 4

    Expert Comment

    I do it manually, like you asked, using VB code. It helps me keep track of users opening files in a Dropbox shared folder.

    It uses a file marker, but a "pretty" one with the username clearly showing. So all one has to do is look at the folder. Also, it's there even if there are no conflicts - my main concern was that users do NOT open simultaneously, and edit divergently (remember I use it for Dropbox, not windows share).

    Place this code inside the "ThisWorkbook" module in the VB editor of any file that you wish to track:

    Option Explicit
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    End Sub
    Private Sub Workbook_Open()
        'watch out, excel does not fire this event at all if SHIFT is pressed
    End Sub
    Public Sub FileOpenPutMarker()
        Dim sFile As String
        Dim iFileNum As Integer
        'On Error Resume Next
        sFile = ThisWorkbook.Path & "\" & Application.UserName & ".has." & & ".open"
        'MsgBox "Put " & sFile
        iFileNum = FreeFile
        Open sFile For Output As iFileNum
        Print #iFileNum, sFile
        Close #iFileNum
    End Sub
    Private Sub FileOpenKillMarker()
        Dim sFile As String
        On Error Resume Next
        sFile = ThisWorkbook.Path & "\" & Application.UserName & ".has." & & ".open"
        Kill sFile
        'If Err.Number <> 53 Then
        '    MsgBox "Error deleting File Open marker: " & Err.Description
        'End If
    End Sub

    Open in new window

    It works about 99% of the time - sometimes file markers are left off (when Excel crashes instead of exiting cleanly, for example). in those cases you can just delete them, or simply open the Excel file and they will be overwritten by a new marker.

    Hope this helps if you want to stick to your original approach.
    LVL 31

    Assisted Solution

    by:Rob Henson

    If you were to search for threads on EE about shared workbooks you would find a resounding"Don't Do It".

    This is a feature in Excel that simply does not work as it should. Workbook sharing causes all sorts of file problems, corruptions etc etc.  As quoted by another EE Expert, there are only two types of Shared Workbook - the corrupted ones and those that soon will be corrupted.

    There is an option in Excel whereby each user can have their details appear. The fields to be completed are in Excel Options. Here each user can put their name and when they have a file open, other users trying to open the file will see the same message but it will say the name rather than "Another User".

    However, it is down to each user to populate their details.

    Also not sure how well it works over mixed OS.

    Rob H
    LVL 4

    Expert Comment

    You need to do following steps from where the file is stored.  go to  My computer --->Manage--Shared Folder-->Open files.
    It will show list of files opend with users used by. Also can see files opened with read and write access. Select the file right click where the user having write access and close open file. You will get write access.

    Author Closing Comment

    Engineer pipped me to the post on this and enabled the workbook sharing part. We have constant backups so we should be ok if things dont for out.

    Cheers Simon

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    If you have done a reformat of your hard drive and proceeded to do a successful Windows XP installation, you may notice that a choice between two operating systems when you start up the machine. Here is how to get rid of this: Click Start Clic…
    The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
    The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now