Excel VSTO add in keeps getting soft disabled after Excel crashes. Any way to run addin.Connect = true when excel restarts

Question

I have developed an excel addin which fetches data from DB. There are some scenarios where the excel becomes non-responsive and crashes.

Then I restart excel, the addin is listed in active AddIns but it does not show up in Ribbon.

Then I have to go to File->Options->AddIns->Com AddIns->Uncheck the checked AddIns, then Same Path again and Check the AddIn again and it shows up.

I need help to check whenever Excel is restarted I should check if add in connected(or visible to users), if no, connect it and show. In my bleak attempt, I tried following:

private void ThisAddIn_Startup(object sender, System.EventArgs e)
    { int i =1;
        try
        {
            foreach (COMAddIn addin in Application.COMAddIns)
            {
                if ( addin.Description.Contains("ExcelAddInNewTest") )
                {
                    // addin.Guid.ToString();
                    addin.Connect = true;
                    MessageBox.Show(addin.Description.ToString());
                    //if (addin.Connect != true)
                    //{
                    //    addin.Connect = true;

                    //}// addin.


                }
            }
        }
        catch(Exception ee)
        {

            MessageBox.Show("Error in Addin Startup");
        }

Any help would be greatly appreciated.

Answer 1

Checking the availability of data at startup can dramatically slow down startup time of the Excel itself, and, in addition, the add-in can lose the connection or get some other exception at any time, which will cause the crash and as result the add-in will be disabled.

So, my solution is more radical: I created another, very small Add-In, that tracks blocking entries in the registry and deletes them in the background every time Excel starts. After Excel is restarted again, all blocked add-ins will be returned.

Try this:

private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
    Action RestoreDisabledAddIns = () =>
    {
        using (var officeKeys = Registry.CurrentUser.OpenSubKey(@"Software\Microsoft\Office"))
        {
            foreach (var keyName in officeKeys.GetSubKeyNames())
            {
                switch (keyName)
                {
                    case "14.0":
                    case "15.0":
                    case "16.0":    
                        var resiliencyKey = officeKeys.OpenSubKey($"{keyName}\\Excel\\Resiliency\\DisabledItems", RegistryKeyPermissionCheck.ReadWriteSubTree);
                        if (resiliencyKey != null)
                        {
                            var valNames = resiliencyKey.GetValueNames();
                            foreach (var valName in valNames)
                            {
                                resiliencyKey.DeleteValue(valName);
                            }
                            resiliencyKey.Close();
                        }
                        break;
                }
            }

            //Enable addins:
            var addinSubKey = officeKeys.OpenSubKey("Excel\\Addins", RegistryKeyPermissionCheck.ReadWriteSubTree);
            if (addinSubKey != null)
            {
                var addIns = new[] {"YourExcelAddIn1", "YoueExcelAddIn2"};
                var subKeys = addinSubKey.GetSubKeyNames();
                foreach (var addIn in addIns.Where(t => subKeys.Contains(t)))
                {
                    var addInKey = addinSubKey.OpenSubKey(addIn, RegistryKeyPermissionCheck.ReadWriteSubTree, RegistryRights.SetValue);
                    if (addInKey != null)
                    {
                        addInKey.SetValue("LoadBehavior", 3, RegistryValueKind.DWord);
                        addInKey.Close();
                    }
                }
            }
            officeKeys.Close();
        }
    };
    Task.Factory.StartNew(RestoreDisabledAddIns);
}