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

    Got It-Not Really: XL File Reservation (XL97; SR2)

    Is it possible to create code to determine who has a specific XL file open? I take it that if person A is working on a file and person B tries to open the same file, person B will receive a file reservation indicating person A is using the file and may select "Read Only, Notify or Cancel".

    Thanks,
    John

  2. #2
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Got It-Not Really: XL File Reservation (XL97; SR2)

    I'm not Excel profiecient, but I know you can setup Workbook sharing so that several users can edit the same sheets in a workbook simultaneously.

    Are you trying to prevent two users from opening the same XL file?
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

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

    Re: Got It-Not Really: XL File Reservation (XL97; SR2)

    Kevin,

    I'm trying to determine if the file has a reservation on it. If it does encounter a file reservation then Sub Quit. I've tried to determine if the file is open in another post but I can't determine if the file is opened by someone else from another server. Therefore I'm trying file reservations.

    Sooner or later I'll get it <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Got It-Not Really: XL File Reservation (XL97; SR2)

    Hi John,
    I don't have Excel97 to test this, but it works in XP and should do what you want:
    <pre> 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
    </pre>

    It's from the MSKB - Q138621.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: Got It-Not Really: XL File Reservation (XL97; SR2)

    Rory,

    Is it possible to replace the function with a sub-routine? I've tried your suggestion w/XL97 and can not quite get it to work.

    Thanks,
    John

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Got It-Not Really: XL File Reservation (XL97; SR2)

    John,
    You can call the function from a subroutine, which is probably easier.
    Something like:
    Sub TestFile()
    If IsFileOpen("yourfilenamehere") Then
    Exit Sub
    Else
    ...do whatever you want to do
    End If
    Does that help? (Note: It won't tell you who has the file open - I'm still working on that one for a post in the Excel forum too)
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Got It-Not Really: XL File Reservation (XL97; SR2)

    I wanted to ask you a question even though I think your problem is solved with Rory's IsFileOpen function. What do you mean by "reservation"?
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

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

    Re: Got It-Not Really: XL File Reservation (XL97; SR2)

    When you retrieve an XL file that someone already has opened you receive a "File Reservation" notification. You can either open as read-only or choose notify to be alerted when it is available.

  9. #9
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Got It-Not Really: XL File Reservation (XL97; SR2)

    Ah, you can tell I spend little time sharing Excel files. I think if you get the IsOpenFuntion to work, you'll have what you need unless you want to capture the name of the user who has the file open.
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

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

    Re: Got It-Not Really: XL File Reservation (XL97; SR2)

    Rory,

    The code you provided works in XL97.

    I didn't realized that one could call the function from a subroutine.

    Thanks,
    John <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>

Posting Permissions

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