How to prevent BeforeCloseEvent firing more than once.

D

Daffo

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.Value
,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
 
G

Gary''s Student

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

Daffo

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
 
D

Daffo

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
 

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