I have this code for creating a worksheet:
public void CreateWorksheet(string activeWorkbookName)
{
bool erroFromExcel = true;
// As per this post:
//https://social.msdn.microsoft.com/Forums/vstudio/en-US/9168f9f2-e5bc-4535-8d7d-4e374ab8ff09/hresult-800ac472-from-set-operations-in-excel?forum=vsto
while (erroFromExcel)
{
try
{
lock (this.excel)
{
Worksheet loaderSheet;
try
{
loaderSheet = (Worksheet)this.excel.Workbooks.Item[activeWorkbookName].Worksheets.Item[LOADER_SHEET_NAME];
//loaderSheet = (Worksheet)this.excel.Workbooks.Application.ActiveWorkbook.Worksheets.Item[LOADER_SHEET_NAME];
loaderSheet.Rows[1].Value2 = "";// Clear cells
loaderSheet.Select();
erroFromExcel = false;//
return;
}
catch
{
// expected exception - currently no data loader sheet
}
loaderSheet = (Worksheet)this.excel.Workbooks.Item[activeWorkbookName].Worksheets.Add();
//loaderSheet = (Worksheet)this.excel.Workbooks.Application.ActiveWorkbook.Worksheets.Add();
string name = this.excel.Workbooks.Application.ActiveWorkbook.FullName;
loaderSheet.Name = LOADER_SHEET_NAME;
loaderSheet.Rows[1].Value2 = "";// Clear cells
erroFromExcel = false;
}
}
catch (Exception e)
{
// swallow exception
}
}
}
This code works very well when opening a new Excel document. But it hangs in the second catch block if I run the code with an existing document with "Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX))" exception. If fails here:
#1 loaderSheet = (Worksheet)this.excel.Workbooks.Item[activeWorkbookName].Worksheets.Add();
If I replace the line above with this one:
#2 loaderSheet = (Worksheet)this.excel.Workbooks.Application.ActiveWorkbook.Worksheets.Add();
everything is OK. Why does it work in the second case and not in the first case?
I inspected the name of the active work book and in both cases it is the same, i.e. the activeWorkbookName parameter passed is the same as this.excel.Workbooks.Application.ActiveWorkbook.FullName and yet #1 crashes and #2 does not. Where is the difference?
Thanks
User contributions licensed under CC BY-SA 3.0