+ Reply to Thread
Results 1 to 4 of 4

Test for open workbook

  1. #1
    Registered User
    Join Date
    06-20-2006
    Posts
    22

    Test for open workbook

    I'm having a few problems when one workbook is open, then another containing macros is opened (while the first is still open). The macro's cease up in this situation and they do funny things.

    So, I am wondering if there is a test procedure to check if there is a workbook open, and if so, then I can send a

    Sub TestWrkBks()
    * If...'OtherWorkbooksAreOpen?'...Then
    MsgBox "Please close all other workbooks and try again"
    ActiveWorkbook.Close
    End if
    End Sub

    Any assistance would be much appreciated.

    Kartune85

  2. #2
    NickHK
    Guest

    Re: Test for open workbook

    kartune85,
    If other workbooks cause problems, it is probably because your code is not
    clearly working only on ThisWorkbook.

    If you add a
    With ThisWorkbook
    With .Worksheets(Whatever)
    .Range(.....

    other workbooks should not have an effect.

    NickHK

    "kartune85" <[email protected]> wrote
    in message news:[email protected]...
    >
    > I'm having a few problems when one workbook is open, then another
    > containing macros is opened (while the first is still open). The
    > macro's cease up in this situation and they do funny things.
    >
    > So, I am wondering if there is a test procedure to check if there is a
    > workbook open, and if so, then I can send a
    >
    > Sub TestWrkBks()
    > * If...'OtherWorkbooksAreOpen?'...Then
    > MsgBox "Please close all other workbooks and try again"
    > ActiveWorkbook.Close
    > End if
    > End Sub
    >
    > Any assistance would be much appreciated.
    >
    > Kartune85
    >
    >
    > --
    > kartune85
    > ------------------------------------------------------------------------
    > kartune85's Profile:

    http://www.excelforum.com/member.php...o&userid=35586
    > View this thread: http://www.excelforum.com/showthread...hreadid=554374
    >




  3. #3
    Registered User
    Join Date
    06-20-2006
    Posts
    22

    Workbook Conflict

    Thanks for the advice NickHK.

    Altho I've still got a slight problem with that I've used "ThisWorkbook" but from my code I've created a second workbook which is automatically named "Book1, Book2, Book3..." and I've got code in "ThisWorkbook" that moves a sheet from itself to the newly created workbook.

    There's a conflict when someone tries to start a new Workbook while another one is still open and then "ThisWorkbook" tries to move a sheet from itself to "Workbooks(2)". Thus the confusion cause I think it gets confused as to which one is "Workbooks(2)" (Book1 or OldBook).

    How can I refer to the newly created workbook without it getting confused with other workbooks that are already opened?

    If there is some kind of testing procedure to stop the workbook from opening if there's others open would come in quite handy.

    Kartune85

  4. #4
    NickHK
    Guest

    Re: Test for open workbook

    kartune85,
    Dim NewWB As WorkBook
    Set NewWB=Workbook.Add (..../ Workbook.Open (....

    Now you can use this reference to the newly added WB in subsequent code and
    it does not matter how many other WBs are open. e.g
    NewWB.Worksheets(1).Range....

    NickHK

    "kartune85" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Thanks for the advice NickHK.
    >
    > Altho I've still got a slight problem with that I've used
    > "ThisWorkbook" but from my code I've created a second workbook which is
    > automatically named "Book1, Book2, Book3..." and I've got code in
    > "ThisWorkbook" that moves a sheet from itself to the newly created
    > workbook.
    >
    > There's a conflict when someone tries to start a new Workbook while
    > another one is still open and then "ThisWorkbook" tries to move a sheet
    > from itself to "Workbooks(2)". Thus the confusion cause I think it gets
    > confused as to which one is "Workbooks(2)" (Book1 or OldBook).
    >
    > How can I refer to the newly created workbook without it getting
    > confused with other workbooks that are already opened?
    >
    > If there is some kind of testing procedure to stop the workbook from
    > opening if there's others open would come in quite handy.
    >
    > Kartune85
    >
    >
    > --
    > kartune85
    > ------------------------------------------------------------------------
    > kartune85's Profile:

    http://www.excelforum.com/member.php...o&userid=35586
    > View this thread: http://www.excelforum.com/showthread...hreadid=554374
    >




+ 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