+ Reply to Thread
Results 1 to 6 of 6

Check if file is open and open if closed

  1. #1
    Registered User
    Join Date
    06-15-2009
    Location
    Isle of Man
    MS-Off Ver
    Excel 2003
    Posts
    27

    Question Check if file is open and open if closed

    Hi,
    I have code which will check if the file "C of S.xls" is open. Ideally, if it is open, the sub will exit but if it it closed, the sub will open "C of S.xls".
    When the file is already open, everything works fine. However, when the file is closed, I get the "subscript out of range, Run time error 9" debug error at the point in the routine where it looks for the "C of S.xls" file.
    How can I avoid this happening?
    I have attached the file.
    Thanks.

    Jockster
    Attached Files Attached Files

  2. #2
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Check if file is open and open if closed

    You can test directly against the functions returned value, no need for extra variables.

    Please Login or Register  to view this content.
    Is it just a typo you say "C of S.xls" when the code is checking for "R of C.xls" ?
    Cheers
    Andy
    www.andypope.info

  3. #3
    Registered User
    Join Date
    06-15-2009
    Location
    Isle of Man
    MS-Off Ver
    Excel 2003
    Posts
    27

    Re: Check if file is open and open if closed

    Yes indeed - a typo.
    Function code still gets stuck when looking for "R of C.xls" when it is not open. When it is open, the Function code doesn't detect this and tries to open "R of C.xls" again. Very frustrating.

  4. #4
    Registered User
    Join Date
    04-14-2010
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    28

    Re: Check if file is open and open if closed

    This code simply checks to see if a file is open,

    Then if uses an IF statement to goto one of either 2 different modules, if a file exists, then you can code it to do A , or if does not , then B

    Hope this helps,

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    06-15-2009
    Location
    Isle of Man
    MS-Off Ver
    Excel 2003
    Posts
    27

    Re: Check if file is open and open if closed

    Thanks elmarko,
    Have tried your code but when I changed the file name to check the 'else' part of the function, I get the debug window with "Run-time error '53': File not found". So, even with the 'on error resume next' and 'on error goto 0' in place, the code still halts and displays the debug window.
    Is there something in place which is cancelling out the error handling 'override'?

  6. #6
    Registered User
    Join Date
    04-14-2010
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    28

    Re: Check if file is open and open if closed

    Quote Originally Posted by Jockster View Post
    Thanks elmarko,
    Have tried your code but when I changed the file name to check the 'else' part of the function, I get the debug window with "Run-time error '53': File not found". So, even with the 'on error resume next' and 'on error goto 0' in place, the code still halts and displays the debug window.
    Is there something in place which is cancelling out the error handling 'override'?
    The function part of the code can be left as it is,

    You only need to edit below the line for the path and what you want it do if it does, or does not exist

    Please Login or Register  to view this content.
    Last edited by elmarko123; 04-22-2010 at 08:25 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1