Hi -

I posted this question yesterday without code because it is written in C#
for a 2007 Excel applicaition and therefore thought it may not get many
looks. But what the heck we'll try it again with code.

So the program flow is populate the worksheet with numbers and text (these
come from a cube and SQL data tables in a SQL database), copy paste it to a
second worksheet in another workbook, go back to the original worksheet and
populate it with new numbers from the next contract, copy paste it to the
second worksheet but below the prior copy/paste section ... this is repeated
many times to as many as 1000 times or more.

The problem I'm having is that as the iteration increases into the 300 or
400's the program slows down to a virtual crawl, sometimes just stopping.

As a shot in the dark it seems like I'm using some resource without
releasing it which causes memory drain.

It works fine through 50 or 100 iterations but as I said it begins to slow
after that. In fact, I'm playing with a workaround which does 50 or so
copy/pastes at a time, then bulk copy the 50 onto a third worksheet. This
seems to work okay ... so that I avoid the "crawl", but it would be great to
understand why the slow down in the first place.

private void btPrintReport_Click(object sender, EventArgs e)
{

Object oMissing = System.Reflection.Missing.Value;

//Setup Workbook that holds "printed" pages
Excel.Workbook printWB =
Globals.ThisWorkbook.Application.Workbooks.Add(oMi ssing);
Excel.Sheets sheets = printWB.Worksheets;

// Rename current sheet to Dummy
Excel.Worksheet reportSheet =
(Excel.Worksheet)printWB.ActiveSheet;
reportSheet.Name = "Report";

// Delete other sheets from workbook
foreach (Excel.Worksheet nameWS in sheets)
if (nameWS.Name != "Report")
nameWS.Delete();

//Set worksheet and page counters
int p = 0;
int n = 0;
int rowCnt = 70;
int scale = 51;

//Get first contract
Globals.Sheet2.tblUserInterface_MasterBindingSourc e.MoveFirst();
n++;

do
{

//Print Contract to Worksheet
p++;
PrintContractToWorksheet2(printWB, p, rowCnt);

//Print Ceded and Net contracts
if (Globals.Sheet2.commonAccountNamedRange.Value2.ToS tring()
== "Y")
{
rbACNCeded.Checked = true;
p++;
PrintContractToWorksheet2(printWB, p, rowCnt);
rbACNNet.Checked = true;
p++;
PrintContractToWorksheet2(printWB, p, rowCnt);

//To reset the ACN check box to Assumed ... needed when
the reset checkbox is set to unchecked.
rbACNAssumed.Checked = true;
}

//Move to next contract and increment

Globals.Sheet2.tblUserInterface_MasterBindingSourc e.MoveNext();
n++;

}
while (n <=
Globals.Sheet2.tblUserInterface_MasterBindingSourc e.Count);


// Remove the workbooks connection that got copied over with
the worksheet.paste command above
//printWB.Connections[1].Delete();

// Move the cursor back to cell a1
Excel.Range printRange =
(Excel.Range)reportSheet.get_Range("a1", oMissing);
printRange.Select();

// Remove the range names from the workbook
// Started at the last name and worked down to the first name
.... if working up the names collection, we wouldn't have an item i to delete
after half of the items are deleted.
for (int i = printWB.Names.Count; i = 1; i--)
if (printWB.Names.Item(i, oMissing,
oMissing).Name.Contains("Print_Area") == false && printWB.Names.Item(i,
oMissing, oMissing).Name.Contains("_") == false) //Keeps the print area
ranges and a couple of other (Hidden) cube related ranges that caused a crash
when they were deleted
printWB.Names.Item(i, oMissing, oMissing).Delete();

//Save the print worksheet
printWB.SaveAs(xlsPath, oMissing, oMissing, oMissing, oMissing,
oMissing, Excel.XlSaveAsAccessMode.xlNoChange, oMissing, oMissing, oMissing,
oMissing, oMissing);
printWB.Close(oMissing, oMissing, oMissing);

}


private void
PrintContractToWorksheet2(Microsoft.Office.Interop .Excel.Workbook printWB,
int p, int rowCnt)
{

Object oMissing = System.Reflection.Missing.Value;

//Method is used to refresh the data linked to the data cube.
// In theory the CalculateUntilAsyncQueriesDone should work
alone but does not and is prone to hang
// Showing the form "frmWorkingForm" with the sleep thread
method afferted the hanging.
// I stumpled onto this workaround by wanting to have the
computer "do something else" until the Aysnc was done; but I really don't
know why it works.
Globals.ThisWorkbook.Application.CalculateUntilAsy ncQueriesDone();
frmWorkingForm workingForm = new frmWorkingForm();
workingForm.Show();

System.Threading.Thread.Sleep(0);

workingForm.Close();

//Copy the results from the template
Globals.Sheet1.Sheet1_Print_Area.Copy(oMissing);

// Set a reference to the sheet named "Report"
Excel.Worksheet reportSheet =
(Excel.Worksheet)printWB.ActiveSheet;

//Activate the print worksheet and select the cell that we are
pasting the results to
((Excel._Worksheet)reportSheet).Activate();
Excel.Range printRange = (Excel.Range)reportSheet.get_Range("a"
+ Convert.ToString((p - 1) * rowCnt + 1), oMissing);
printRange.Select();

//For the first iteration we have to paste the column widths ...
after that we have to put in a page break
if (p == 1)

printRange.PasteSpecial(Microsoft.Office.Interop.E xcel.XlPasteType.xlPasteColumnWidths,
Microsoft.Office.Interop.Excel.XlPasteSpecialOpera tion.xlPasteSpecialOperationNone, oMissing, oMissing);

//Paste results then paste values the results
// The first paste results is used to get the text box to copy
.... and I'm not aware if any other way to bring them over; it also brings
number formats.
reportSheet.Paste(oMissing, oMissing);

printRange.PasteSpecial(Microsoft.Office.Interop.E xcel.XlPasteType.xlPasteValues,
Microsoft.Office.Interop.Excel.XlPasteSpecialOpera tion.xlPasteSpecialOperationNone, oMissing, oMissing);

//Add Solid black line and PageBreak
printRange = (Excel.Range)reportSheet.get_Range("a" +
Convert.ToString((p) * rowCnt), "w" + Convert.ToString((p) * rowCnt));
printRange.Select();

printRange.Borders.get_Item(Microsoft.Office.Inter op.Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlLineStyle.xlContinuous;

printRange.Borders.get_Item(Microsoft.Office.Inter op.Excel.XlBordersIndex.xlEdgeBottom).Weight = Excel.XlBorderWeight.xlMedium;

printRange = (Excel.Range)reportSheet.get_Range("a" +
Convert.ToString((p) * rowCnt + 1), "ag" + Convert.ToString((p) * rowCnt +
1));
printRange.Select();
reportSheet.HPageBreaks.Add(printRange);

}