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

[Zoo]

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.)

 

[Ardus Petus]

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

 

[Zoo]

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.

 

[Guest]

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