Excel Addin - get active workbook

0

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

c#
vsto
excel-addins
asked on Stack Overflow Sep 17, 2019 by user1523271

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0