none
VSTO : SheetBeforeDelete event problem C# RRS feed

  • Question

  • Hi everyone,

    I’m currently developing a VSTO addin for Excel where I would like to add a SheetBeforeDelete event.

    When I’m loading the addin, I’m loading an Excel template at the same time. Then, the worksheets are made visible as the user is clicking on the ribbon’s functionalities.

    The problem is, when the user is deleting a worksheet and using again the functionality, an error occurs. That’s why I have added a SheetBeforeDelete event in order to reload the worksheet from the template and allow the user clicking on the functionality again.

    I tried several things but still doesn’t work:

    private void app_SheetBeforeDelete(object Sh)
            {
                deletedsheetname = Globals.ThisAddIn.Application.ActiveSheet.Name;
    
                object templateFile = System.IO.Path.GetFullPath("MyPath//Workbook1.xlsx");
                Excel.Application excelApplication = new Excel.Application();
    
                Excel.Workbook templatebook;
                Excel.Worksheet wkshtocopy = null;
    
                try
                {
                    templatebook = excelApplication.Workbooks.Add(templateFile);
    
                    for (int i = 1; i < templatebook.Worksheets.Count; i++)
                    {
                        if (templatebook.Worksheets[i].Name == deletedsheetname)
                        {
                            wkshtocopy = (Excel.Worksheet)templatebook.Sheets[i];
                            wkshtocopy.Name = "TempSheet";
    
                            wkshtocopy.Copy(After: Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets[1]);
    
                        }
                    }
                }
                catch (Exception e)
                {
                    Console.WriteLine(e);
                }
            }

    I found this solution :

    " The excelApplication instance belong to a newly created Excel instance, but the parameter comes from the add-in instance.

    If you need to copy the worksheet you need to open it in the existing Excel instance instead."

    But if I put Globals.ThisAddIn.Application.Workbooks.Open(templateFile), Excel stop working

    Thank you very much for your help !

    Regards,


    • Edited by Djesshz Wednesday, November 2, 2016 11:48 AM Specify the language
    Wednesday, November 2, 2016 11:40 AM

Answers

  • Hi,

    >>if I use Globals.ThisAddIn.Application to retrieve the current instance of Excel, shouldn’t it work?

    Yes, it should work. But Office doesn’t support to open other workbooks in SheetBeforeDelete event apparently. I test in other events like WorkbookNewSheet/SheetFollowHyperlink/,SheetBeforeRightClick they all work fine to open a new workbook.

    For your case, I think you should reload the worksheet when users use the functionality instead of deleting the sheet.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Djesshz Monday, November 7, 2016 11:02 AM
    Sunday, November 6, 2016 7:29 AM
    Moderator

All replies

  • Hello,

    You can't cope a worksheet from another Excel instance.

    > The problem is, when the user is deleting a worksheet and using again the functionality, an error occurs.

    What error are you talking about? Could you please be more specific?


    [custom.development]

    • Marked as answer by Djesshz Thursday, November 3, 2016 9:35 AM
    • Unmarked as answer by Djesshz Thursday, November 3, 2016 9:35 AM
    Wednesday, November 2, 2016 5:56 PM
  • Hello,

    I understand, but if I use Globals.ThisAddIn.Application to retrieve the current instance of Excel, shouldn’t it work?

    If not, how do I get my Excel instance back knowing that it is declared in another project?

    I didn’t find the way to share variables/instances from one project to another (both in the same solution)

    Solution (2 projects)

    + VSTO Addin Excel

    • ThisAddIn.cs

    (Where I want to retrieve my Excel instance)

    + Winform

    • Form1.cs

    (Where my Excel instance is declared)

    Thank you very much for your help

    Regards,

    Thursday, November 3, 2016 9:39 AM
  • Hi,

    >>if I use Globals.ThisAddIn.Application to retrieve the current instance of Excel, shouldn’t it work?

    Yes, it should work. But Office doesn’t support to open other workbooks in SheetBeforeDelete event apparently. I test in other events like WorkbookNewSheet/SheetFollowHyperlink/,SheetBeforeRightClick they all work fine to open a new workbook.

    For your case, I think you should reload the worksheet when users use the functionality instead of deleting the sheet.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Djesshz Monday, November 7, 2016 11:02 AM
    Sunday, November 6, 2016 7:29 AM
    Moderator
  • Thank you very much for your answer

    I tried to reload the worksheet when users use the functionality again and it's working

    Many thanks,

    Regards,

    Monday, November 7, 2016 11:03 AM