Who has an excel file open?

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?
leehewsonAsked:
Who is Participating?
 
mega666Commented:
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 :   http://support.microsoft.com/kb/269860

:)
0
 
Arman KhodabandeIT Manager and ConsultantCommented:
Hi
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!
0
 
Arman KhodabandeIT Manager and ConsultantCommented:
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.
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
pgorodCommented:
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)

    FileOpenKillMarker
    
End Sub

Private Sub Workbook_Open()

    'watch out, excel does not fire this event at all if SHIFT is pressed

    FileOpenPutMarker
    FixRowHeights

    
End Sub

Public Sub FileOpenPutMarker()

    Dim sFile As String
    Dim iFileNum As Integer
        
    'On Error Resume Next
    
    sFile = ThisWorkbook.Path & "\" & Application.UserName & ".has." & ThisWorkbook.name & ".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
    Err.Clear
    sFile = ThisWorkbook.Path & "\" & Application.UserName & ".has." & ThisWorkbook.name & ".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.
0
 
Rob HensonFinance AnalystCommented:
@mega666:

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.

@leehewson:
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.

Thanks
Rob H
0
 
sravi2208Commented:
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.
0
 
leehewsonAuthor Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.