I recently upgraded to Office 2010 and I am using the Microsoft.Office.Interop.Excel version 14.0 in a c# project. My code opens one workbook and copies a sheet into a second workbook. This was working fine in the previous version but now when it copies it is missing all of the chart objects. Anyone have any ideas? Here is my code:
public void BundleFiles(List edList, string bundledFileName) { string sReportTemplateFileName = "";
ArrayList ioErrorSheets = new ArrayList();
sReportTemplateFileName = System.Configuration.ConfigurationManager.AppSettings["ExhibitExportTemplateDir"] + "ReportTemplate.xls";
Microsoft.Office.Interop.Excel.Workbooks books = COMExcelApplication.Workbooks;
Microsoft.Office.Interop.Excel.Workbook wbTarget = books.Open(sReportTemplateFileName, 0, false , 5, Type.Missing, Type.Missing, true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, Type.Missing, false, false, 0, false, false, Microsoft.Office.Interop.Excel.XlCorruptLoad.xlNormalLoad);
Microsoft.Office.Interop.Excel.Sheets targetSheets = wbTarget.Sheets;
Microsoft.Office.Interop.Excel.Workbook wb = null;
int i = 0;
foreach (ExhibitDocument ed in edList)
{
string sTargetFile = System.Configuration.ConfigurationManager.AppSettings["ExhibitExportOutputDir"] + ed.OutputFileName;
wb = books.Open(sTargetFile, 0, true, 5, Type.Missing, Type.Missing, true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, Type.Missing, false, false, 0, false, false, Microsoft.Office.Interop.Excel.XlCorruptLoad.xlNormalLoad);
Microsoft.Office.Interop.Excel.Sheets sheets = wb.Sheets;
foreach (Microsoft.Office.Interop.Excel.Worksheet ws in sheets)
{
//ws.Copy(Type.Missing, wbTarget.Worksheets[1 + i]);
ws.Copy(Type.Missing, targetSheets.get_Item(1 + i));
i++;
Marshal.FinalReleaseComObject(ws);
Marshal.FinalReleaseComObject(sheets);
}
}
// delete the first sheet, it will be empty
Microsoft.Office.Interop.Excel.Worksheet wsTarget = targetSheets.get_Item(1);
if (targetSheets.Count > 1)
wsTarget.Delete();
bundledFileName = System.Configuration.ConfigurationManager.AppSettings["ExhibitExportOutputDir"] + bundledFileName;
wbTarget.SaveAs(bundledFileName,
Type.Missing, Type.Missing,
Type.Missing, Type.Missing,
Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
Type.Missing, Type.Missing,
Type.Missing, Type.Missing, false);
wbTarget.Close(false, Type.Missing, Type.Missing);
if (wb != null)
{
wb.Close(false, Type.Missing, Type.Missing);
Marshal.FinalReleaseComObject(wb);
}
Marshal.FinalReleaseComObject(wsTarget);
Marshal.FinalReleaseComObject(targetSheets);
Marshal.FinalReleaseComObject(books);
Marshal.FinalReleaseComObject(wbTarget);
wsTarget = null;
wb = null;
targetSheets = null;
books = null;
wbTarget = null;
}