Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to prevent BeforeCloseEvent firing more than once.
Hi i am using Excel 2000 and Visual studio 2003.
I am opening more than one workbook in a single window of excel and also using single excel instance to open the workbooks. I am logging the data changes for each workbook during the BeforeCloseEvent. The problem i am facing is that even if i close one workbook, the BeforeCloseEvent gets fired more than once.The number of times the BeforeCloseEvent gets fired is equal to the number of workbooks that are open in the window. I want to kno if there is a way to prevent the BeforeCloseEvent getting fired more than once when i close a workbook. This is how i am opening the workbooks. Tracker.UseDelegate(xlAppNew,Fnameparameter); is called whenever a user selects a file from dropdown. namespace FinalIntegrate { public class DataTrackerForm : System.Windows.Forms.Form { public Excel.Application xlAppNew; private Excel.Workbook xlBook; private Excel.Worksheet xlSheet ; public void UseDelegate(Excel.Application xlAppFrom,string strFilepath) { xlAppNew=xlAppFrom; xlBook = xlAppNew.Workbooks.Open(strFilepath,3,Missing.Valu e ,Missing.Value , Missing.Value ,Missing.Value ,Missing.Value ,Missing.Value ,Missing.Value ,Missing.Value ,Missing.Value ,Missing.Value ,Missing.Value ); xlAppNew.ActiveWorkbook.Saved=false; EventDel_BeforeBookClose = new Excel.AppEvents_WorkbookBeforeCloseEventHandler( BeforeBookClose); xlAppNew.WorkbookBeforeClose += EventDel_BeforeBookClose; EventDel_BeforeSave= new Excel.AppEvents_WorkbookBeforeSaveEventHandler( BeforeBookSave); xlAppNew.WorkbookBeforeSave += EventDel_BeforeSave; EventDel_CellsChange = new Excel.DocEvents_ChangeEventHandler( CellsChange); for (int i=1; i<=xlAppNew.Sheets.Count; i++) { xlSheet= (Excel.Worksheet)xlBook.Worksheets.get_Item(i); xlSheet.Change += EventDel_CellsChange; } xlAppNew.Visible = true; xlAppNew.UserControl = true; }// end usedelegate private void BeforeBookSave(Excel.Workbook Wb, bool SaveAsUI , ref bool Cancel) { DataLog(); } private void BeforeBookClose(Excel.Workbook Wb, ref bool Cancel ) { if(!Wb.Saved) { DialogResult response; response = MessageBox.Show("Do you want to save changes made to"+ Wb.Name+"? " , "DataTracker", MessageBoxButtons.YesNo); switch (response) { case DialogResult.Yes : Wb.Save(); break; case DialogResult.No : Wb.Saved=true; xlAppNew.WorkbookBeforeClose -= EventDel_BeforeBookClose; break; }// end switch }// end if else Wb.Saved=true; }// end if }//end BeforeClose } } Suppose i hav 3 files(A.xls, B.xls,C.xls) opened in a single window of excel. I make changes to A.xls and close it. then the data changes get logged thrice instead of once.But the MessageBox which asks for Do you want to save changes gets displayed only once...... I dont know where i have gone wrong.. Please help me in this regard.. With Regards, Daffo |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to prevent BeforeCloseEvent firing more than once.
Simply put a test in your macro to check the number of open Excel workbooks.
Have the desired BeforeClose Event logic operate only if the number of open workbooks is equal to one. -- Gary's Student "Daffo" wrote: Hi i am using Excel 2000 and Visual studio 2003. I am opening more than one workbook in a single window of excel and also using single excel instance to open the workbooks. I am logging the data changes for each workbook during the BeforeCloseEvent. The problem i am facing is that even if i close one workbook, the BeforeCloseEvent gets fired more than once.The number of times the BeforeCloseEvent gets fired is equal to the number of workbooks that are open in the window. I want to kno if there is a way to prevent the BeforeCloseEvent getting fired more than once when i close a workbook. This is how i am opening the workbooks. Tracker.UseDelegate(xlAppNew,Fnameparameter); is called whenever a user selects a file from dropdown. namespace FinalIntegrate { public class DataTrackerForm : System.Windows.Forms.Form { public Excel.Application xlAppNew; private Excel.Workbook xlBook; private Excel.Worksheet xlSheet ; public void UseDelegate(Excel.Application xlAppFrom,string strFilepath) { xlAppNew=xlAppFrom; xlBook = xlAppNew.Workbooks.Open(strFilepath,3,Missing.Valu e ,Missing.Value , Missing.Value ,Missing.Value ,Missing.Value ,Missing.Value ,Missing.Value ,Missing.Value ,Missing.Value ,Missing.Value ,Missing.Value ); xlAppNew.ActiveWorkbook.Saved=false; EventDel_BeforeBookClose = new Excel.AppEvents_WorkbookBeforeCloseEventHandler( BeforeBookClose); xlAppNew.WorkbookBeforeClose += EventDel_BeforeBookClose; EventDel_BeforeSave= new Excel.AppEvents_WorkbookBeforeSaveEventHandler( BeforeBookSave); xlAppNew.WorkbookBeforeSave += EventDel_BeforeSave; EventDel_CellsChange = new Excel.DocEvents_ChangeEventHandler( CellsChange); for (int i=1; i<=xlAppNew.Sheets.Count; i++) { xlSheet= (Excel.Worksheet)xlBook.Worksheets.get_Item(i); xlSheet.Change += EventDel_CellsChange; } xlAppNew.Visible = true; xlAppNew.UserControl = true; }// end usedelegate private void BeforeBookSave(Excel.Workbook Wb, bool SaveAsUI , ref bool Cancel) { DataLog(); } private void BeforeBookClose(Excel.Workbook Wb, ref bool Cancel ) { if(!Wb.Saved) { DialogResult response; response = MessageBox.Show("Do you want to save changes made to"+ Wb.Name+"? " , "DataTracker", MessageBoxButtons.YesNo); switch (response) { case DialogResult.Yes : Wb.Save(); break; case DialogResult.No : Wb.Saved=true; xlAppNew.WorkbookBeforeClose -= EventDel_BeforeBookClose; break; }// end switch }// end if else Wb.Saved=true; }// end if }//end BeforeClose } } Suppose i hav 3 files(A.xls, B.xls,C.xls) opened in a single window of excel. I make changes to A.xls and close it. then the data changes get logged thrice instead of once.But the MessageBox which asks for Do you want to save changes gets displayed only once...... I dont know where i have gone wrong.. Please help me in this regard.. With Regards, Daffo |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to prevent BeforeCloseEvent firing more than once.
Hi Gary thank you fr replying.. But I dont want to use macros...is
there any otherway that i can use to accomplish this...Since i am using C# to open the workbook and log the data when the Workbook is closed , i dont want to use an macro. With Regards Daffo Gary''s Student wrote: Simply put a test in your macro to check the number of open Excel workbooks. Have the desired BeforeClose Event logic operate only if the number of open workbooks is equal to one. -- Gary's Student "Daffo" wrote: Hi i am using Excel 2000 and Visual studio 2003. I am opening more than one workbook in a single window of excel and also using single excel instance to open the workbooks. I am logging the data changes for each workbook during the BeforeCloseEvent. The problem i am facing is that even if i close one workbook, the BeforeCloseEvent gets fired more than once.The number of times the BeforeCloseEvent gets fired is equal to the number of workbooks that are open in the window. I want to kno if there is a way to prevent the BeforeCloseEvent getting fired more than once when i close a workbook. This is how i am opening the workbooks. Tracker.UseDelegate(xlAppNew,Fnameparameter); is called whenever a user selects a file from dropdown. namespace FinalIntegrate { public class DataTrackerForm : System.Windows.Forms.Form { public Excel.Application xlAppNew; private Excel.Workbook xlBook; private Excel.Worksheet xlSheet ; public void UseDelegate(Excel.Application xlAppFrom,string strFilepath) { xlAppNew=xlAppFrom; xlBook = xlAppNew.Workbooks.Open(strFilepath,3,Missing.Valu e ,Missing.Value , Missing.Value ,Missing.Value ,Missing.Value ,Missing.Value ,Missing.Value ,Missing.Value ,Missing.Value ,Missing.Value ,Missing.Value ); xlAppNew.ActiveWorkbook.Saved=false; EventDel_BeforeBookClose = new Excel.AppEvents_WorkbookBeforeCloseEventHandler( BeforeBookClose); xlAppNew.WorkbookBeforeClose += EventDel_BeforeBookClose; EventDel_BeforeSave= new Excel.AppEvents_WorkbookBeforeSaveEventHandler( BeforeBookSave); xlAppNew.WorkbookBeforeSave += EventDel_BeforeSave; EventDel_CellsChange = new Excel.DocEvents_ChangeEventHandler( CellsChange); for (int i=1; i<=xlAppNew.Sheets.Count; i++) { xlSheet= (Excel.Worksheet)xlBook.Worksheets.get_Item(i); xlSheet.Change += EventDel_CellsChange; } xlAppNew.Visible = true; xlAppNew.UserControl = true; }// end usedelegate private void BeforeBookSave(Excel.Workbook Wb, bool SaveAsUI , ref bool Cancel) { DataLog(); } private void BeforeBookClose(Excel.Workbook Wb, ref bool Cancel ) { if(!Wb.Saved) { DialogResult response; response = MessageBox.Show("Do you want to save changes made to"+ Wb.Name+"? " , "DataTracker", MessageBoxButtons.YesNo); switch (response) { case DialogResult.Yes : Wb.Save(); break; case DialogResult.No : Wb.Saved=true; xlAppNew.WorkbookBeforeClose -= EventDel_BeforeBookClose; break; }// end switch }// end if else Wb.Saved=true; }// end if }//end BeforeClose } } Suppose i hav 3 files(A.xls, B.xls,C.xls) opened in a single window of excel. I make changes to A.xls and close it. then the data changes get logged thrice instead of once.But the MessageBox which asks for Do you want to save changes gets displayed only once...... I dont know where i have gone wrong.. Please help me in this regard.. With Regards, Daffo |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to prevent BeforeCloseEvent firing more than once.
Hi Gary thank you fr replying.. But I dont want to use macros...is
there any otherway that i can use to accomplish this...I want to open morre than one workbook at a time but the BeforeCloseEvent should get fired only for the workbook i have closed...(i.e only once)Since i am using C# to open the workbook and log the data when the Workbook is closed , i dont want to use an macro. With Regards Daffo Gary''s Student wrote: Simply put a test in your macro to check the number of open Excel workbooks. Have the desired BeforeClose Event logic operate only if the number of open workbooks is equal to one. -- Gary's Student "Daffo" wrote: Hi i am using Excel 2000 and Visual studio 2003. I am opening more than one workbook in a single window of excel and also using single excel instance to open the workbooks. I am logging the data changes for each workbook during the BeforeCloseEvent. The problem i am facing is that even if i close one workbook, the BeforeCloseEvent gets fired more than once.The number of times the BeforeCloseEvent gets fired is equal to the number of workbooks that are open in the window. I want to kno if there is a way to prevent the BeforeCloseEvent getting fired more than once when i close a workbook. This is how i am opening the workbooks. Tracker.UseDelegate(xlAppNew,Fnameparameter); is called whenever a user selects a file from dropdown. namespace FinalIntegrate { public class DataTrackerForm : System.Windows.Forms.Form { public Excel.Application xlAppNew; private Excel.Workbook xlBook; private Excel.Worksheet xlSheet ; public void UseDelegate(Excel.Application xlAppFrom,string strFilepath) { xlAppNew=xlAppFrom; xlBook = xlAppNew.Workbooks.Open(strFilepath,3,Missing.Valu e ,Missing.Value , Missing.Value ,Missing.Value ,Missing.Value ,Missing.Value ,Missing.Value ,Missing.Value ,Missing.Value ,Missing.Value ,Missing.Value ); xlAppNew.ActiveWorkbook.Saved=false; EventDel_BeforeBookClose = new Excel.AppEvents_WorkbookBeforeCloseEventHandler( BeforeBookClose); xlAppNew.WorkbookBeforeClose += EventDel_BeforeBookClose; EventDel_BeforeSave= new Excel.AppEvents_WorkbookBeforeSaveEventHandler( BeforeBookSave); xlAppNew.WorkbookBeforeSave += EventDel_BeforeSave; EventDel_CellsChange = new Excel.DocEvents_ChangeEventHandler( CellsChange); for (int i=1; i<=xlAppNew.Sheets.Count; i++) { xlSheet= (Excel.Worksheet)xlBook.Worksheets.get_Item(i); xlSheet.Change += EventDel_CellsChange; } xlAppNew.Visible = true; xlAppNew.UserControl = true; }// end usedelegate private void BeforeBookSave(Excel.Workbook Wb, bool SaveAsUI , ref bool Cancel) { DataLog(); } private void BeforeBookClose(Excel.Workbook Wb, ref bool Cancel ) { if(!Wb.Saved) { DialogResult response; response = MessageBox.Show("Do you want to save changes made to"+ Wb.Name+"? " , "DataTracker", MessageBoxButtons.YesNo); switch (response) { case DialogResult.Yes : Wb.Save(); break; case DialogResult.No : Wb.Saved=true; xlAppNew.WorkbookBeforeClose -= EventDel_BeforeBookClose; break; }// end switch }// end if else Wb.Saved=true; }// end if }//end BeforeClose } } Suppose i hav 3 files(A.xls, B.xls,C.xls) opened in a single window of excel. I make changes to A.xls and close it. then the data changes get logged thrice instead of once.But the MessageBox which asks for Do you want to save changes gets displayed only once...... I dont know where i have gone wrong.. Please help me in this regard.. With Regards, Daffo |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to Prevent Duplicate Data from inputing using input application? | Excel Worksheet Functions | |||
How to Prevent Duplicate Data from inputing using input application? | Excel Discussion (Misc queries) | |||
How to Prevent Duplicate Data from inputing using input application? | New Users to Excel | |||
Prevent Consecutive Entries | Excel Worksheet Functions | |||
How can I prevent access to code contained within Tab | Excel Worksheet Functions |