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

    Determining if XL file is Open (XL97; SR2)

    Is it possible to determine if an XL file is already opened or being used by someone? I would like to create code as to the following:

    If the XL file is open then
    Do nothing
    Else
    Run code
    End If

    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: Determining if XL file is Open (XL97; SR2)

    I use this function (in a class mod) gleaned from various posts on the Lounge. There are problems if the app does not implement "file sharing". For example, notepad.exe does not lock open files in a shared environment. So, this function will not work with .txt files opened with notepad. It should work fine with .xls files. You'll have to tweak the code since it's pulled from the class mod.

    Function FileInUse(strFileName As String) As Boolean
    pFullName = strFileName
    On Error Resume Next
    Open pFullName For Binary Access Read Write Lock Read Write As #1
    'Open pFullName For Input Lock Read As 1 ??
    Close #1
    Select Case Err.Number
    Case 75 'Path/File Access error
    Case 70 'Permission Denied
    End Select
    FileInUse = Err.Number
    End Function
    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
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Determining if XL file is Open (XL97; SR2)

    Here's another flavor using the FileSystemObject instead of the *obsolete* Open statement:

    Function FileInUse(strFileName As String) As Boolean
    pFullName = strFileName
    On Error Resume Next
    Set oTs = oFso.OpenTextFile(pFullName, ForWriting, False)
    Select Case Err.Number
    Case 53 'File not found
    MsgBox "The file does not exists.", vbCritical, "clsFSO - FileInUse Error"
    Case 75 'Path/File Access error
    Case 70 'Permission Denied
    End Select
    Set oTs = Nothing
    FileInUse = Err.Number
    End Function
    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>

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Determining if XL file is Open (XL97; SR2)

    I haven't tried your code, but I would think that you would have to check the error number before you close the file. It looks to me like the Select Case statement is checking the error code from the Close statement not the Open.
    Legare Coleman

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

    Re: Determining if XL file is Open (XL97; SR2)

    Technically I suppose you are correct. However, both the open and the close produce the same error if the file in question has not been closed from the time the open statement executes until the close statement executes -- about ohh, humm, I dunno, .0001ms??? (Just a guess. I have concept of time)
    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>

Posting Permissions

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