Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" a ecrit dans le message de ... 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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" wrote in message ... 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" a ecrit dans le message de ... 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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" wrote in message ... 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" a ecrit dans le message de ... 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
beforeclose placement | Excel Discussion (Misc queries) | |||
EnableEvents BeforeClose | Excel Programming | |||
BeforeClose Problem | Excel Programming | |||
BeforeClose vs Auto_Close | Excel Programming | |||
How to get out of BeforeClose event without closing? | Excel Programming |