Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Identifying Who Has File Open (XL97: SR2)

    Is it possible to determine who has an Excel file open? Perhaps using the file reservation? The following function can determine if a file is open but does not indicate who has it open. I can not locate any information in MSoft's KB. The only thing close is KB 213383 but it is similar to what I mention below.

    Function IsFileOpen(filename As String)
    Dim filenum As Integer, errnum As Integer

    On Error Resume Next ' Turn error checking off.
    filenum = FreeFile() ' Get a free file number.
    ' Attempt to open the file and lock it.
    Open filename For Input Lock Read As #filenum
    Close filenum ' Close the file.
    errnum = Err ' Save the error number that occurred.
    On Error GoTo 0 ' Turn error checking back on.

    ' Check to see which error occurred.
    Select Case errnum

    ' No error occurred.
    ' File is NOT already open by another user.
    Case 0
    IsFileOpen = False

    ' Error number for "Permission Denied."
    ' File is already opened by another user.
    Case 70
    IsFileOpen = True

    ' Another error occurred.
    Case Else
    Error errnum
    End Select
    End Function

    Thanks,
    John

  2. #2
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Identifying Who Has File Open (XL97: SR2)

    I started a thread some time ago about this very thing, as I was playing with Ian Sharpe's Work Menu for Excel. I can't find the URL to where I obtained his code but it seems to encapsulate what you have posted.

    No one in the lounge could offer a solution then, if you find one please let me know.
    I suspect that you will need to resort to API calls to obtain the information.

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Identifying Who Has File Open (XL97: SR2)

    I found something on XL97's Visual Basic Help regarding "WriteReserved Property".

    If the active workbook is write-reserved, this example displays a message that contains the name of the user who saved the workbook as write-reserved.

    With ActiveWorkbook
    If .WriteReserved = True Then
    MsgBox "Please contact " & .WriteReservedBy & Chr(13) & _
    " if you need to insert data in this workbook."
    End If
    End With

    I can't seem to get it to work pointing to a specific path/file.

    John

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Identifying Who Has File Open (XL97: SR2)

    If someone saves a file with the option to have a password to modify it, the file is "writereserved" and it is "writereservedby" the USERNAME of the person who saved it that way.

    It is NOT the person using that file at the present time. The property is available for an OPEN workbook. You can NOT access it on a closed workbook.

    You want info on who has the file open BEFORE you open it. I don't think excel/VB can do it directly. I think, if anything, you have to work with API and I never have done this.

    FYI the userstatus property can tell you (if the file is SHARED) who (else) has the file open. It must be opened to check the userstatus.

    Steve

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Identifying Who Has File Open (XL97: SR2)

    Steve,

    ***"You want info on who has the file open BEFORE you open it. I don't think excel/VB can do it directly. I think, if anything, you have to work with API and I never have done this."***

    This is exactly what I am after. Unfortunately I have little or no experience with API. Hopefully someone will come up with a solution.

    Thanks,
    John

  6. #6
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Identifying Who Has File Open (XL97: SR2)

    Steve,

    I've found another site that has code for determining who has a document file open. Now all I have to do is figure out how I can incorporate it as part of XL.

    John

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Identifying Who Has File Open (XL97: SR2)

    You can incorporate it easily into excel by copying the code into a module:
    BUT, 2 things to be aware of:

    1) it will NOT work for Excel files (or most other files!) It only works for WORD files and any other programs that when a file is opened it creates a copy of the file on the drive and appending "~$" before the name and appends the "file opener's userid" to the userid of this "temp file". Most programs do NOT do this. The function works by opening THIS NEWLY CREATED file which has the userid of the one who has opened the "file of interest". It does NOT really "open" the file you are concerned about, it opens the copy, (which does NOT give you an error).

    2) If you have windows explorer set to "do not show hidden files and folders" (tools - folder options - "view" tab) then it will NOT work for even WORD files since if the file is HIDDEN, you will NOT be allowed to open this "hidden file".

    Steve

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Identifying Who Has File Open (XL97: SR2)

    I hope that your supply of <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> is sufficient. This question has been asked many times in the Microsoft newsgroups, but I haven't been able to find a thread with a satisfactory answer.
    Among the suggestions given are:
    <UL><LI>Make workbooks shareable (Tools | Share Workbook...). You can then open a workbook and find out who is using it exclusive or shared from the UserStatus property of the workbook; this is an array with three columns: user name, date/time of opening; status (1=exclusive, 2=shared).
    <LI>Put macros in every workbook that write the username into a text file on the server with the date/time of opening and closing the workbook (not very attractive in my opinion)
    <LI>If you are a network administrator, you can query server statistics.[/list]I haven't found a solution with or without Windows API calls that just tells you who has an Excel file open...

  9. #9
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Identifying Who Has File Open (XL97: SR2)

    You're right (as usual), the code only works with Word documents. I'll just have to wait & see if someone comes up with an API solution.

    In the mean time I'm going to have a <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> . It may be a long one if no one responds.

    John

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Identifying Who Has File Open (XL97: SR2)

    Maybe this page gives a starting point? I cannot test any of that, since I have Win98.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  11. #11
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Identifying Who Has File Open (XL97: SR2)

    Would someone kindly test this code for the age old question "Who has the file open". The code is available in VBA and APi.

    The code is suppose to work even if the file is located on a network drive and is open by someone else. Therefore if you are using this code from your desktop and the file is located on a network drive you should be returned the username of the individual who has the file open.

    I have tried it with XL 2002 and have not had any success in being returned the username. The username returned is " " (blank).

    Thanks,
    John

  12. #12
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Identifying Who Has File Open (XL97: SR2)

    John,

    I have tested it under XP, with Xl 2002,

    The IsFileAlreadyOpen and LastUser functions worked fine for me.

    IsFileAlreadyOpen returns True/False
    LastUser Returned the user name if the file was open. If it is not open it returned #VALUE, if there was more than one user it returned " ".

    Hope it helps. (Wish I had the code some time ago...)

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Identifying Who Has File Open (XL97: SR2)

    The code works OK for me too, in Excel 2002 (SP-2). I have tested both with a workbook on a local drive and with a workbook on a shared network drive. Have you made sure that the path and file name you provide are exactly correct?

  14. #14
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Identifying Who Has File Open (XL97: SR2)

    Hans,

    I am having an issue where a workbook is on a shared network drive. It works when I test a file on a local drive.

    If the workbook on the shared network drive is open, a message will be returned that the file is opened but the lastuser name is still blank. I get the same results if I use XL installed on my local or network drive. I double checked to see that the username was setup in XL (Tools>Options>General>UserName).

    I am also using XL 2002, SP2.

    John

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Identifying Who Has File Open (XL97: SR2)

    I can only suggest that you try to single-step through the code and try to find out where it fails.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •