#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2009
    Posts
    65
    Rep Power
    15

    How to check to see if file is open?


    In my script I need to write to a file but not sure how to check to see if file is open or not...any ideas? TIA.
  2. #2
  3. No Profile Picture
    Grumpier old Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2003
    Posts
    14,436
    Rep Power
    4539
    Do you mean opened by your code or opened by some other application?
    ======
    Doug G
    ======
    Bartender to Rene Descartes "have another beer?" Descartes: "I think not" and he vanished.
    --Alfred Bester
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2009
    Posts
    65
    Rep Power
    15
    some other application i.e. file is already open by a different process
  6. #4
  7. No Profile Picture
    Grumpier old Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2003
    Posts
    14,436
    Rep Power
    4539
    I don't know the answer, but a couple guesses. One, you could just try operating on the file from your code with appropriate error trapping in case the file is opened exclusively by another app.

    Or there may be some Windows API function available that will let you know. I don't know of anything in VB, but I haven't really used VB since VB6.
    ======
    Doug G
    ======
    Bartender to Rene Descartes "have another beer?" Descartes: "I think not" and he vanished.
    --Alfred Bester
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2009
    Posts
    65
    Rep Power
    15
    Thanks for the response. Though I couldn't find a function, I was able to work around this, here's how:

    Since I was writing this file, and wanted to check to see if it was open, I first checked to see if I was able to delete the file or not...and deletion wasn't possible if the file was open. And, if I was able to delete the file, then that meant that file was not open...I guess this also means that my original question wasn't incomplete in that since I am (re)writing this file, deleting it was alright.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Posts
    289
    Rep Power
    44
    The answer to your question is far more complex than you have imagined. When a file is opened, it can be opened with specific restrictions on file locking and/or record locking (VB actually uses block locking vs actual record locking). The restrictions on file locking are:
    NOLOCK = 0 (no lock applied)
    RDLOCK = 1 (locked to all other read attempts)
    WRLOCK = 2 (locked to all other Write attempts)
    RWLOCK = 3 (locked to all other read or write attempts)

    For example, NotePad opens up a text file and reads it into a memory/disk buffer. Any other program can come along and make changes to the file while you supposedly have it open. So when you save the file, you overwrite all the changes that the other program made. On the other end of the scale, system files used by the operating system may always be locked out to a normal user. File management has to be done with proper forethought.

    In answer to your original question, file access should always be error trapped, as there can be many different types of file errors, some of which are recoverable, and some which are fatal. For example:
    "That device appears Unavailable."
    "Insert a Disk in the Drive"
    "Internal Disk Error."
    "Disk is Full. Continue?"
    "That Filename is Illegal!"
    "File in use by another user!"
    "Path does not Exist!"
    "Bad File Mode!"
    "File is Already Open."
    "Read Attempt Past End of File."

    PM me if you would like a copy of an open file routine I wrote many moons ago.
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2009
    Posts
    65
    Rep Power
    15
    Your point is well taken. Here's the scenario I was dealing with:

    1. user invokes a program that creates & opens (Excel) file
      Excel file is kept open i.e. user forgets to close Excel
      user invokes same program by typing the same (excel) file name


    The last step (above) poses a program for my VBScript in that it "dies" ungracefully because the same Excel file exists and is open and because of this the same name excel file cannot be generated.

    Hence, I want a mechanism to alert the user about this - how can I do this? In other words, what VBScript functions can be used to find file locking? I know how to code but don't know if there's a VBScript function for (excel) file.

    Thanks for your time.
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2009
    Posts
    65
    Rep Power
    15
    Meant to say that...

    The last step (above) poses a problem for my VBScript...
  16. #9
  17. No Profile Picture
    Grumpier old Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2003
    Posts
    14,436
    Rep Power
    4539
    Like I mentioned, you should make sure you're doing proper error handling, particularly around the code where you attempt to open the new excel file. Your code will get an appropriate error if the new file can't be created, then you can decide what you want your code to do if the file creation fails.

    vbscript has limited error trapping using on error resume next and on error goto 0 statements.
    ======
    Doug G
    ======
    Bartender to Rene Descartes "have another beer?" Descartes: "I think not" and he vanished.
    --Alfred Bester
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Posts
    289
    Rep Power
    44
    These are VB6 routines for opening a file. If OpenFile is successful, it returns a handle to the file. If the file is locked, it tries 3 times and then passes it to the error routine. The error routine calls FileErrors to determine what action to take.

    J.A. Coutts
    Code:
    Function OpenFile(Filename$, Mode%, RLock%, RecordLen%) As Integer
        Const REPLACEFILE = 1, READAFILE = 2, ADDTOFILE = 3
        Const RANDOMFILE = 4, BINARYFILE = 5
        Const NOLOCK = 0, RDLOCK = 1, WRLOCK = 2, RWLOCK = 3
        Dim ErrorCode As Long
        Dim FileNum%, Action%, LockFlg%
        LockFlg% = 0
        FileNum% = FreeFile
        On Error GoTo OpenErrors
        Select Case Mode
            Case REPLACEFILE
                Select Case RLock%
                    Case NOLOCK
                        Open Filename For Output Shared As FileNum%
                    Case RDLOCK
                        Open Filename For Output Lock Read As FileNum%
                    Case WRLOCK
                        Open Filename For Output Lock Write As FileNum%
                    Case RWLOCK
                        Open Filename For Output Lock Read Write As FileNum%
                End Select
            Case READAFILE
                Select Case RLock%
                    Case NOLOCK
                        Open Filename For Input Shared As FileNum%
                    Case RDLOCK
                        Open Filename For Input Lock Read As FileNum%
                    Case WRLOCK
                        Open Filename For Input Lock Write As FileNum%
                    Case RWLOCK
                        Open Filename For Input Lock Read Write As FileNum%
                End Select
            Case ADDTOFILE
                Select Case RLock%
                    Case NOLOCK
                        Open Filename For Append Shared As FileNum%
                    Case RDLOCK
                        Open Filename For Append Lock Read As FileNum%
                    Case WRLOCK
                        Open Filename For Append Lock Write As FileNum%
                    Case RWLOCK
                        Open Filename For Append Lock Read Write As FileNum%
                End Select
            Case RANDOMFILE
                Select Case RLock%
                    Case NOLOCK
                        Open Filename For Random Shared As FileNum% Len = RecordLen%
                    Case RDLOCK
                        Open Filename For Random Lock Read As FileNum% Len = RecordLen%
                    Case WRLOCK
                        Open Filename For Random Lock Write As FileNum% Len = RecordLen%
                    Case RWLOCK
                        Open Filename For Random Lock Read Write As FileNum% Len = RecordLen%
                End Select
            Case BINARYFILE
                Select Case RLock%
                    Case NOLOCK
                        Open Filename For Binary Shared As FileNum%
                    Case RDLOCK
                        Open Filename For Binary Lock Read As FileNum%
                    Case WRLOCK
                        Open Filename For Binary Lock Write As FileNum%
                    Case RWLOCK
                        Open Filename For Binary Lock Read Write As FileNum%
                End Select
            Case Else
              Exit Function
        End Select
        OpenFile = FileNum%
        Exit Function
    OpenErrors:
        If Err = 70 Then  'File is locked, try 3 times
            LockFlg% = LockFlg% + 1
            Debug.Print "File Locked!"
            If LockFlg > 3 Then GoTo OpenErrCont
            Resume
        End If
    OpenErrCont:
        ErrorCode = Err
        Action% = FileErrors(ErrorCode)
        Select Case Action%
            Case 0
              Resume            'Resumes at line where ERROR occured
            Case 1
                Resume Next     'Resumes at line after ERROR
            Case 2
                OpenFile = 0     'Unrecoverable ERROR-reports error, exits function with error code
                Exit Function
            Case Else
                MsgBox Error$(Err) + vbCrLf + "After line " + Str$(Erl) + vbCrLf + "Program will TERMINATE!"
                'Unrecognized ERROR-reports error and terminates.
                End
        End Select
    End Function
    Code:
    Function FileErrors(errVal As Long) As Integer
        Dim Msg$, msgType%, Response%
    'Return Value 0=Resume,              1=Resume Next,
    '             2=Unrecoverable Error, 3=Unrecognized Error
    msgType% = 48
    Select Case errVal
        Case 68
          Msg$ = "That device appears Unavailable."
          msgType% = msgType% + 4
        Case 71
          Msg$ = "Insert a Disk in the Drive"
        Case 53
          Msg$ = "Cannot Find File"
          msgType% = msgType% + 5
       Case 57
          Msg$ = "Internal Disk Error."
          msgType% = msgType% + 4
        Case 61
          Msg$ = "Disk is Full.  Continue?"
          msgType% = 35
        Case 64, 52
          Msg$ = "That Filename is Illegal!"
        Case 70
          Msg$ = "File in use by another user!"
          msgType% = msgType% + 5
        Case 76
          Msg$ = "Path does not Exist!"
          msgType% = msgType% + 2
        Case 54
          Msg$ = "Bad File Mode!"
        Case 55
          Msg$ = "File is Already Open."
        Case 62
          Msg$ = "Read Attempt Past End of File."
        Case Else
          FileErrors = 3
          Exit Function
      End Select
      FileErrors = 2
    End Function
  20. #11
  21. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Posts
    1
    Rep Power
    0
    This one is one of the nice way for executing the code in accurate way thanks for sharing.
  22. #12
  23. No Profile Picture
    Grumpier old Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2003
    Posts
    14,436
    Rep Power
    4539
    couttsj please use forum CODE tags around posted code.
    ======
    Doug G
    ======
    Bartender to Rene Descartes "have another beer?" Descartes: "I think not" and he vanished.
    --Alfred Bester
  24. #13
  25. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Posts
    16
    Rep Power
    0

    open file issue


    wow we got same problem here but thanks to those who responded from it. it was really helpful guys..thanks
  26. #14
  27. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Posts
    16
    Rep Power
    0

    how to check for open files


    i've got a problem on deleting my folder but it always says that after i click the delete button it says file is still open on other document or something but i can't see any open files there. please help guys..
  28. #15
  29. No Profile Picture
    Grumpier old Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2003
    Posts
    14,436
    Rep Power
    4539
    This topic is closed. Irrelevant replies are not helpful.
    Originally Posted by george88smith88
    i've got a problem on deleting my folder but it always says that after i click the delete button it says file is still open on other document or something but i can't see any open files there. please help guys..
    If there is anything remotely related to visual basic in your comment start a new topic with a proper question.

    .
    ======
    Doug G
    ======
    Bartender to Rene Descartes "have another beer?" Descartes: "I think not" and he vanished.
    --Alfred Bester

IMN logo majestic logo threadwatch logo seochat tools logo