Results 1 to 9 of 9
  1. #1
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    test if file locked (Excel 97/2000)

    I'm adding a logging feature to an Excel application and need to work around the minute possibility when multiple users are acccssing this log file at the same time. This is the code I use to open/write to the file:
    <pre>fh = FreeFile
    Open LogName For Append Lock Write As #fh
    </pre>

    If the file is currently being accessed, how can I test for that condition before continuing? I tested this by leaving the logfile open in Notepad then running the code that writes to it. The code ran fine with no error. The new log entry was of course not in this instance of Notepad but when I closed and reopend the log file, the new entry was there. This tells me that the "lock" didn't do anything or did it? Maybe this isn't a problem after all. <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16>

    How does this lock thing work anyway? Is this even something I need to worry about? The application that writes to the log file is used by no more than 20 users.

    Thnx, Deb <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: test if file locked (Excel 97/2000)

    I can't say what the Write Lock does, but I have noticed that NotePad appears to be non-locking, so that isn't the best test. Try Word.

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: test if file locked (Excel 97/2000)

    The only method I've found is trying to copy the file to another folder prior to opening it. That should return a trappable error if the file is being written.
    Charlotte

  4. #4
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Jeddah, Saudi Arabia
    Posts
    243
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: test if file locked (Excel 97/2000)

    If another program has the file open you should get an error 70 - Permission Denied using the code you posted.

  5. #5
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: test if file locked (Excel 97/2000)

    Whatever checks you make, it is still possible to get a conflict at the exact time when you try to open the file.

    So you have to trap any errors that occur when you open the file and handle them.

    This makes any other checks superfluous.

    StuartR

  6. #6
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: test if file locked (Excel 97/2000)

    Yeh, you're right, I'll just have to add the right On Error checks. Neither NotePad or TextPad report any errors if they have the log file open when I write a new entry. This entry is spooled somewhere because when the file is closed and re-opened it does appear.

    I'll test it again by opening in Word as another poster said since it will generate the error so I know what to catch.

    The docs do not say how the Lock attribute works and neither do any books I've looked at. I don't have this problem with my Perl apps running on Unix, it's a straight forward procedure to genuinely lock a file for write.

    Thnx, Deb <img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15>

  7. #7
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: test if file locked (Excel 97/2000)

    Since the most likely conflict is two copies of your code running at the same time, the best way to test it is with another copy of your own code.

    What I often do in these circumstances is single step one copy until it has the resource locked and then run the other one to see what happens.

    StuartR

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: test if file locked (Excel 97/2000)

    Not entirely. If you try issue an Open on a file that is in the process of being written, say as the result of a file being received by a communications program, you can truncate the file without triggering an error. At least, that was our experience.
    Charlotte

  9. #9
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: test if file locked (Excel 97/2000)

    Nice point, and this doesn't involve a timing window since the communications program would be running from the instant the file is created until it has completed writing it.

    StuartR

Posting Permissions

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