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