+ Reply to Thread
Results 1 to 4 of 4

How can I detect .xls closing? Not BeforeClose , Like AfterClose?

  1. #1
    Zoo
    Guest

    How can I detect .xls closing? Not BeforeClose , Like AfterClose?

    My .xls file connects to a Oracle Server.
    And I want to keep the connection alive while the file is alive.

    So I wrote the code like below.

    -- a.xls --

    Public oCon As Object
    Private Sub Workbook_Open()
    Set oCon = CreateObject("ADODB.Connection")
    oCon.Open "DataSourceName", "ID", "PASSWORD"
    End Sub
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    oCon.Close
    Set oCon = Nothing
    End Sub



    But there's a problem.
    When I open xls files , i.e , a.xls (the file mentioned above) , b.xls
    (ordinary xls file) , c.xls (ordinary xls file),
    and edit b.xls or c.xls ?
    Workbook_BeforeClose of a.xls runs , and after that , I'm asked to
    overwrite b.xls (or c.xls) , then I cancel the operation.
    In this case, after all, a.xls is still opend , but the connection is
    already lost.

    How I can turn aroud this?
    I don't want to connect the server again.
    My client wants not to disturb the server log.
    For him,repeating connection and disconnection is disturbing the log.

    (By the way , I posted similar topic before this.
    At that time, I developed xla file.
    Concerning xla file , the same problem can be avoided using Deactivate
    event.
    But , concerning xls file, deactivate event is not useful because it is
    raised so much.)


  2. #2
    Ardus Petus
    Guest

    Re: How can I detect .xls closing? Not BeforeClose , Like AfterClose?

    Your code (Workbook_BeforeClose) should not run when you close b.xls or
    c.xls.
    It runs only when you close a.xls.

    If not, I'm puzzled!

    HTH
    --
    AP

    "Zoo" <[email protected]> a ecrit dans le message de
    news:[email protected]...
    > My .xls file connects to a Oracle Server.
    > And I want to keep the connection alive while the file is alive.
    >
    > So I wrote the code like below.
    >
    > -- a.xls --
    >
    > Public oCon As Object
    > Private Sub Workbook_Open()
    > Set oCon = CreateObject("ADODB.Connection")
    > oCon.Open "DataSourceName", "ID", "PASSWORD"
    > End Sub
    > Private Sub Workbook_BeforeClose(Cancel As Boolean)
    > oCon.Close
    > Set oCon = Nothing
    > End Sub
    >
    >
    >
    > But there's a problem.
    > When I open xls files , i.e , a.xls (the file mentioned above) , b.xls
    > (ordinary xls file) , c.xls (ordinary xls file),
    > and edit b.xls or c.xls ?
    > Workbook_BeforeClose of a.xls runs , and after that , I'm asked to
    > overwrite b.xls (or c.xls) , then I cancel the operation.
    > In this case, after all, a.xls is still opend , but the connection is
    > already lost.
    >
    > How I can turn aroud this?
    > I don't want to connect the server again.
    > My client wants not to disturb the server log.
    > For him,repeating connection and disconnection is disturbing the log.
    >
    > (By the way , I posted similar topic before this.
    > At that time, I developed xla file.
    > Concerning xla file , the same problem can be avoided using Deactivate
    > event.
    > But , concerning xls file, deactivate event is not useful because it is
    > raised so much.)
    >




  3. #3
    Zoo
    Guest

    Re: How can I detect .xls closing? Not BeforeClose , Like AfterClose?

    When you press the 'X' button of b.xls windows intending to close only
    b.xls,
    what you said is right.

    But when you press the 'X' button of Excel Application intending to close
    all the books and quit Excel,
    the macro runs before the dialog (confirmation of overwriting) appears.


    "Ardus Petus" <[email protected]> wrote in message
    news:[email protected]...
    > Your code (Workbook_BeforeClose) should not run when you close b.xls or
    > c.xls.
    > It runs only when you close a.xls.
    >
    > If not, I'm puzzled!
    >
    > HTH
    > --
    > AP
    >
    > "Zoo" <[email protected]> a ecrit dans le message de
    > news:[email protected]...
    > > My .xls file connects to a Oracle Server.
    > > And I want to keep the connection alive while the file is alive.
    > >
    > > So I wrote the code like below.
    > >
    > > -- a.xls --
    > >
    > > Public oCon As Object
    > > Private Sub Workbook_Open()
    > > Set oCon = CreateObject("ADODB.Connection")
    > > oCon.Open "DataSourceName", "ID", "PASSWORD"
    > > End Sub
    > > Private Sub Workbook_BeforeClose(Cancel As Boolean)
    > > oCon.Close
    > > Set oCon = Nothing
    > > End Sub
    > >
    > >
    > >
    > > But there's a problem.
    > > When I open xls files , i.e , a.xls (the file mentioned above) , b.xls
    > > (ordinary xls file) , c.xls (ordinary xls file),
    > > and edit b.xls or c.xls ?
    > > Workbook_BeforeClose of a.xls runs , and after that , I'm asked to
    > > overwrite b.xls (or c.xls) , then I cancel the operation.
    > > In this case, after all, a.xls is still opend , but the connection is
    > > already lost.
    > >
    > > How I can turn aroud this?
    > > I don't want to connect the server again.
    > > My client wants not to disturb the server log.
    > > For him,repeating connection and disconnection is disturbing the log.
    > >
    > > (By the way , I posted similar topic before this.
    > > At that time, I developed xla file.
    > > Concerning xla file , the same problem can be avoided using Deactivate
    > > event.
    > > But , concerning xls file, deactivate event is not useful because it is
    > > raised so much.)
    > >

    >
    >



  4. #4
    Tom Ogilvy
    Guest

    Re: How can I detect .xls closing? Not BeforeClose , Like AfterClo

    Guess you would need to remove the X for the excel application so this
    mistake can not be made.

    http://support.microsoft.com/kb/213502/EN-US/
    XL2000: How to Programmatically Disable Microsoft Excel Control Menu Commands

    --
    Regards,
    Tom Ogilvy




    "Zoo" wrote:

    > When you press the 'X' button of b.xls windows intending to close only
    > b.xls,
    > what you said is right.
    >
    > But when you press the 'X' button of Excel Application intending to close
    > all the books and quit Excel,
    > the macro runs before the dialog (confirmation of overwriting) appears.
    >
    >
    > "Ardus Petus" <[email protected]> wrote in message
    > news:[email protected]...
    > > Your code (Workbook_BeforeClose) should not run when you close b.xls or
    > > c.xls.
    > > It runs only when you close a.xls.
    > >
    > > If not, I'm puzzled!
    > >
    > > HTH
    > > --
    > > AP
    > >
    > > "Zoo" <[email protected]> a ecrit dans le message de
    > > news:[email protected]...
    > > > My .xls file connects to a Oracle Server.
    > > > And I want to keep the connection alive while the file is alive.
    > > >
    > > > So I wrote the code like below.
    > > >
    > > > -- a.xls --
    > > >
    > > > Public oCon As Object
    > > > Private Sub Workbook_Open()
    > > > Set oCon = CreateObject("ADODB.Connection")
    > > > oCon.Open "DataSourceName", "ID", "PASSWORD"
    > > > End Sub
    > > > Private Sub Workbook_BeforeClose(Cancel As Boolean)
    > > > oCon.Close
    > > > Set oCon = Nothing
    > > > End Sub
    > > >
    > > >
    > > >
    > > > But there's a problem.
    > > > When I open xls files , i.e , a.xls (the file mentioned above) , b.xls
    > > > (ordinary xls file) , c.xls (ordinary xls file),
    > > > and edit b.xls or c.xls ?
    > > > Workbook_BeforeClose of a.xls runs , and after that , I'm asked to
    > > > overwrite b.xls (or c.xls) , then I cancel the operation.
    > > > In this case, after all, a.xls is still opend , but the connection is
    > > > already lost.
    > > >
    > > > How I can turn aroud this?
    > > > I don't want to connect the server again.
    > > > My client wants not to disturb the server log.
    > > > For him,repeating connection and disconnection is disturbing the log.
    > > >
    > > > (By the way , I posted similar topic before this.
    > > > At that time, I developed xla file.
    > > > Concerning xla file , the same problem can be avoided using Deactivate
    > > > event.
    > > > But , concerning xls file, deactivate event is not useful because it is
    > > > raised so much.)
    > > >

    > >
    > >

    >
    >


+ 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