Save Method Of Workbook Class Failed

[Mark]

Hi -

I know this is an Excel VBA forum but thought I give the it a try with a C#
program that automates Excel 2007 using VSTO (VS 2008).

I have attached the code below ... the error happens at "printWB.Save();"

I am getting a the information message from Excel of " 'workbookname.xlsx'
in use. Try again later." followed by the exception error "Save Method of
Workbook Class Failed". The messages come intermittantly and therefore am
having a time of it understanding when it happens no less why it happens.

The idea is for the program to run through a list of contracts, populate an
excel sheet with data from a OLAP data cube (server based) for each contract,
make a copy of the excel sheet and save it to a second worbook (values only).
The second workbook is saved after every 25 contracts inorder to reduce
computer resources as the program runs.

Any suggestions are welcome.

Thank you Mark

private void PrintSelectedContract1PerPage()
{

Object oMissing = System.Reflection.Missing.Value;

//Set Print Version
string printVersion = "1 per page";

//Setup Workbook that holds printed pages
printWB =
Globals.ThisWorkbook.Application.Workbooks.Add(oMissing);

Excel.Sheets sheets = printWB.Worksheets;
Excel.Worksheet printSheet =
(Excel.Worksheet)printWB.Worksheets[1];
printSheet.Name = reportName;


//Get File name and path
string xlsPath = @"C:\SaveToFile\myfile.xlsx";

//Save workbook
printWB.SaveAs(xlsPath, oMissing, oMissing, oMissing, oMissing,
oMissing, Excel.XlSaveAsAccessMode.xlNoChange, oMissing, oMissing, oMissing,
oMissing, oMissing);

//Set worksheet and page counters
int n = 0; //The total number of contracts
int p = 0; //The total number of contracts including ceded and
net contracts
int rowCnt = 36;
int scale = 52;

//Get first contract
Globals.Sheet2.tblUserInterface_MasterBindingSource.MoveFirst();

n++;

do
{

//Tell user which contract its on
Globals.ThisWorkbook.Application.StatusBar = n.ToString() +
" of " +
Globals.Sheet2.tblUserInterface_MasterBindingSource.Count.ToString();

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



//In order to improve performance we save the worksheet and
run the Garbage Collector every 25 contracts
if (n % 25 == 0 || n ==
Globals.Sheet2.tblUserInterface_MasterBindingSource.Count)
{

WorkingForm();

printWB.Save(); // => The ERROR MESSAGE OCCURS HERE <=

printWB.Close(oMissing, oMissing, oMissing);


System.Runtime.InteropServices.Marshal.ReleaseComObject(printWB);

GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();

printWB = null;

printWB =
Globals.ThisWorkbook.Application.Workbooks.Open(xlsPath, oMissing, oMissing,
oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
oMissing, oMissing, oMissing, oMissing, oMissing);

}

//Move to next contract and increment

Globals.Sheet2.tblUserInterface_MasterBindingSource.MoveNext();
n++;


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

//Save the print worksheet
printWB.Save();
printWB.Close(oMissing, oMissing, oMissing);

//Clear Status Bar
Globals.ThisWorkbook.Application.StatusBar = null;

//Clean up references and run Garbage Collectopn

System.Runtime.InteropServices.Marshal.ReleaseComObject(printWB);

GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();

printWB = null;

}

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

Object oMissing = System.Reflection.Missing.Value;

WorkingForm();

Excel.Range printRange = null;

if (p == 1)
{
if (printVersion == "1 per page")
Globals.Sheet1.Sheet1_HeaderArea.Copy(oMissing);
else
Globals.Sheet12.Sheet12_HeaderArea.Copy(oMissing);

printRange =
(Excel.Range)((Excel.Worksheet)printWB.Worksheets[1]).Cells[1, 1];

printRange.PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteColumnWidths,
Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, oMissing, oMissing);

printRange.PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteFormats,
Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, oMissing, oMissing);

printRange.PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteValues,
Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, oMissing, oMissing);

}

//Copy the results from the template
if (printVersion == "1 per page")
Globals.Sheet1.Sheet1_ReportArea.Copy(oMissing);
else
Globals.Sheet12.Sheet12_ReportArea.Copy(oMissing);

//Determine the cell that we are pasting the results to
printRange =
(Excel.Range)((Excel.Worksheet)printWB.Worksheets[1]).Cells[(p - 1) * rowCnt
+ 4, 1];

//Paste results then paste values the results

printRange.PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteFormats,
Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, oMissing, oMissing);

printRange.PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteValues,
Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, oMissing, oMissing);

//Clear Clipboard
Clipboard.Clear(); //Not sure this does anything
}

//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 doen; but I really don't
know why it works.
private static void WorkingForm()
{


Globals.ThisWorkbook.Application.CalculateUntilAsyncQueriesDone();
frmWorkingForm workingForm = new frmWorkingForm();
workingForm.Show();

System.Threading.Thread.Sleep(0);

workingForm.Close();
}