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

Z

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

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
 
Z

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top