+ Reply to Thread
Results 1 to 14 of 14

Test if file is open

  1. #1
    Forum Contributor
    Join Date
    12-02-2008
    Location
    Brisbane
    MS-Off Ver
    2016
    Posts
    811

    Test if file is open

    How can I open Book2.xls (in the same folder) in 'read only' mode, using a macro from Book1.xls? Book2 might already be open or it may not. I then need to return control to Book1.
    Last edited by BRISBANEBOB; 01-21-2009 at 05:36 PM.

  2. #2
    Valued Forum Contributor mudraker's Avatar
    Join Date
    11-10-2003
    Location
    Melbourne, Australia
    Posts
    3,983
    This command will open the book
    As it is opened froma macro, the macro in book1 still has control.
    Depending on what your macro is doing next or how it it written you may need to add a command to activate/select book1
    Please Login or Register  to view this content.
    Please Read Forum Rules Before Posting
    Wrap VBA code by selecting the code and clicking the # icon or Read This
    How To Cross Post politely

    Top Excel links for beginners to Experts

    If you are pleased with a member's answer then use the Scales icon to rate it
    If my reply has assisted or failed to assist you I welcome your Feedback.

  3. #3
    Forum Contributor
    Join Date
    12-02-2008
    Location
    Brisbane
    MS-Off Ver
    2016
    Posts
    811

    Thanks

    That simple, huh? Easy when you know how...

    Thanks - much appreciated - you wouldn't believe the code I was writing to try to do that!

  4. #4
    Forum Contributor
    Join Date
    12-02-2008
    Location
    Brisbane
    MS-Off Ver
    2016
    Posts
    811

    Not quite

    I've added the code as:-

    Sub OnOpen()

    Workbooks.Open Filename:=ThisWorkbook.Path & "\Extract V2.0.xls", ReadOnly:=True

    End Sub

    It doesn't appear to do anything. It doesn't crash, it just doesn't open the other book. I'm pretty sure I've spelt everything right and the path is definately right.

    Where am I going wrong?

  5. #5
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Your post does not comply with Rule 5 of our Forum RULES. We have Seven question forums: Miscellaneous, General, Programming, Worksheet Functions, Charting, Excel 2007 Help and New Users. Please choose the appropriate forum, and post your question in ONLY one forum.

    Thread moved

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here
    _________________________________________


    Credo Elvem ipsum etian vivere
    _________________________________________
    A message for cross posters

    Please remember to wrap code.

    Forum Rules

    Please add to your signature if you found this link helpful. Excel links !!!

  6. #6
    Forum Contributor
    Join Date
    12-02-2008
    Location
    Brisbane
    MS-Off Ver
    2016
    Posts
    811

    Code tags

    I assume I'm using the code tags incorrectly because the other book still doesn't open. What I have is:

    Please Login or Register  to view this content.
    Last edited by mudraker; 01-20-2009 at 03:33 AM. Reason: Correcting use of code tags

  7. #7
    Valued Forum Contributor mudraker's Avatar
    Join Date
    11-10-2003
    Location
    Melbourne, Australia
    Posts
    3,983
    BRISBANEBOB

    When using code tags in your posting the whole vba macro code gets placed inside them that includes the Sub & end sub commands

    I will edit your last posting so that it is done correctly

    this macro tests if the file exits - If it does not it will open the FileOpen dialog box so you can see what path it it looking at & also see & select a file that is n the folder

    Please Login or Register  to view this content.

  8. #8
    Forum Contributor
    Join Date
    12-02-2008
    Location
    Brisbane
    MS-Off Ver
    2016
    Posts
    811
    When I run the code from the View Code, it opens the file perfectly. When I have it as Sub OnOpen(), it doesn't and it doesn't give any error messages or crash.

    I assume I have something wrong with the OnOpen bit: I've used OnOpen before and it always executes when the file is opened.

  9. #9
    Forum Expert
    Join Date
    12-29-2004
    Location
    Michigan, USA
    MS-Off Ver
    2013
    Posts
    2,208
    Hi Bob.

    What you are looking for is the Workbook_Open event in the ThisWorkbook module. Post back if you need help moving the procedure to the correct location.

    Jason

  10. #10
    Forum Contributor
    Join Date
    12-02-2008
    Location
    Brisbane
    MS-Off Ver
    2016
    Posts
    811
    Hi Jason

    It works! It works! Thanks for that.

    The only minor issue is that the workbook on the screen is the one the sub opens - is there any way to 'return' the screen to the first workbook?

  11. #11
    Forum Expert
    Join Date
    12-29-2004
    Location
    Michigan, USA
    MS-Off Ver
    2013
    Posts
    2,208
    Sure. In the Workbook_Open procedure, include:

    Please Login or Register  to view this content.
    HTH

    Jason

  12. #12
    Forum Contributor
    Join Date
    12-02-2008
    Location
    Brisbane
    MS-Off Ver
    2016
    Posts
    811

    Thanks

    That works a treat. Is there anything you can do to test if the target worksheet is already open and 'abort' the Workbook_OPen routine if it is?

  13. #13
    Forum Expert
    Join Date
    12-29-2004
    Location
    Michigan, USA
    MS-Off Ver
    2013
    Posts
    2,208
    Put this at the beginning of the procedure:

    Please Login or Register  to view this content.
    Jason

  14. #14
    Forum Contributor
    Join Date
    12-02-2008
    Location
    Brisbane
    MS-Off Ver
    2016
    Posts
    811
    Thanks Jason

+ 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