Hi,
I am creating a book of spreadsheets based on a predefined set of sheets to add. When I add one of my sheets with functions in the cells, specifically EOMONTH, it gets replaced by the named range Print_Area from my Cover Sheet..
I have attached the result file, so you can see the problem. The sheet named "Page 1" shows the incorrect result (there are other formatting issues that appear as well). I also attached the source workbook so you can see the original formula.
Here is the code I use to add this sheet:
*******************************************
sheetLocation = templateLocation + "Cover_e.xls";
if (File.Exists(sheetLocation))
{
destWorkbook_e = excelEngine.Excel.Workbooks.Open(sheetLocation);
destWorkbook_e.Worksheets[destWorkbook_e.Worksheets.Count - 1].Activate();
wsPage = destWorkbook_e.ActiveSheet;
wsPage.Range[wsPage.Names["ReportTitle"].Value].Text = prodName;
wsPage.Range[wsPage.Names["TheDate"].Value].Value = theDate;
sheetLocation = templateLocation + "ToC_e.xls";
if (File.Exists(sheetLocation))
{
sourceWorkbook = excelEngine.Excel.Workbooks.Open(sheetLocation);
destWorkbook_e.Worksheets.AddCopy(sourceWorkbook.Worksheets[0], ExcelWorksheetCopyFlags.CopyAll);
sourceWorkbook.Close();
destWorkbook_e.Worksheets[destWorkbook_e.Worksheets.Count - 1].Activate();
wsPage = destWorkbook_e.ActiveSheet;
wsPage.Range[wsPage.Names["TheDate"].Value].Value = theDate;
}
string prevCompCode = "0-0";
string compCode = "";
foreach (DataRow drReps in dtReps.Rows)
{
string pageNo = drReps["pageno"].ToString();
bool isAnnualized = Convert.ToBoolean(drReps["is_annualized"]);
string universeId = drReps["universeid"].ToString();
compCode = pageNo + "-" + drReps["chart_id"].ToString();
if (compCode != prevCompCode)
{
sheetLocation = templateLocation + drReps["chart_name"] + "_e.xls";
if (File.Exists(sheetLocation))
{
sourceWorkbook = excelEngine.Excel.Workbooks.Open(sheetLocation);
destWorkbook_e.Worksheets.AddCopy(sourceWorkbook.Worksheets[0], ExcelWorksheetCopyFlags.CopyAll);
sourceWorkbook.Close();
destWorkbook_e.Worksheets[destWorkbook_e.Worksheets.Count - 1].Activate();
wsPage = destWorkbook_e.ActiveSheet;
wsPage.Name = "Page " + pageNo;
wsPage.Range[wsPage.Names["Base_Date"].Value].Value = theDate;
wsPage.Range[wsPage.Names["Annualized"].Value].Value = ((isAnnualized) ? "y" : "n");
wsPage.Range[wsPage.Names["CalendarFlag"].Value].Value = ((isCalendar) ? "y" : "n");
wsPage.Range[wsPage.Names["UniverseID"].Value].Value = universeId;
}
prevCompCode = compCode;
}
}
//if (destWorkbook_e.Worksheets.Count > 1) destWorkbook_e.Worksheets[0].Remove();
destWorkbook_e.Worksheets[0].Activate();
destWorkbook_e.SaveAs(sSaveAs, ExcelSaveType.SaveAsXLS);
destWorkbook_e.Close();
}
*******************************************
When I debug the code and check the sourceWorkbook sheet for the range D4, it shows the correct value in the cell: sourceWorkbook.Worksheets[0].Range["D4"].Value="=EOMONTH(D5,IF(Annualized="Y",D2,D1))+1".
But when I add the sheet to the desination Workbook, the same range changes: destWorkbook_e.ActiveSheet.Range["D4"].Value="=CoverPage!Print_Area(D5,IF(Annualized="Y",D2,D1))+1".
Can you tell me what I am doing wrong or is this a glitch in your software?
Thanks,
Mark
ExcelTemplates_bde9932e.zip
