2

VSTO
VS2008 SP1
.NET 3.5
Excel 2007

I am a .net noob. I am trying to load an automation addin that is an excel application/automation addin (it is a dll not xla or xll) from within a vsto addin in the ThisAddIn_Startup() method of the vsto addin. From google I got the below solution which is not working. 

private void ThisAddIn_Startup(object sender, System.EventArgs e)
{

        Application excel = Globals.ThisAddIn.Application;
        //Also tried without display alerts being set to false
        excel.DisplayAlerts = false;
        foreach (AddIn addin in excel.AddIns)
        {
            if (addin.progID.Equals("MY_ADDIN_PROG_ID"))
            {
                Debug.WriteLine("Addin installed is " + addin.Installed);
                addin.Installed = false;
                Debug.WriteLine("Addin is: " + addin.FullName + ", " + addin.progID);
                Debug.WriteLine("Addin installed is " + addin.Installed);
            }
        }
        AddIn addIn = excel.AddIns.Add("MY_ADDIN_PROG_ID", false);
        addIn.Installed = true;
        excel.DisplayAlerts = true;
        Debug.WriteLine("Addin is: " + addIn.FullName + ", " + addIn.progID);
        Debug.WriteLine("Addin installed is " + addIn.Installed);
        excel.DisplayAlerts = false;
        //OTHER STARTUP CODE
        Debug.WriteLine("Starting up addin!");
}

Note, I can see the addin.installed is being set to false and back to true on startup but when I try to populate worksheet with udfs from the addin I tried to load in a later button_click method, I get #NAME? error. I am at my wits end. Any help will be greatly appreciated.

If I first try to call the udf in excel by typing it in a cell by hand before I call my button click method, the worksheet population works and the udfs get evaluted as expected but this is not ideal.

Also setting installed property to true does not seem to be doing anything as i can still see the udf addin as inactive in excel, it is only if I type it into a cell that it gets activated. Is there anything else I need to do to activate the automation addin in my vsto startup?

Thanks!

Kiru
  • 3,489
  • 1
  • 25
  • 46
Buki
  • 21
  • 1
  • 3

4 Answers4

0

I'm not sure you want to do this in the startup event. I have done something similar but not quite the same before which may be applicable. I exposed some COM visible functions to VBA in a different event handler:

protected override object RequestComAddInAutomationService()
{
    // return something com-visible
}

So maybe you can try to load your automation dll this way? This happens before the startup event fires... Excel might be doing something like locking its list of addins while a startup event is being handled - who knows? If it were possible to know Excel programming would be less tedious.

  • Something that is unfortunate about the approaches above is that you can't override GetType(), so you can't make it COM-invisible once you've made the class COM-visible :( –  May 01 '12 at 16:57
  • Thanks Richard, unfortunately i did not write the automation addin so cannot refactor it. I was hoping to leverage some of the udfs in in it by programmatically populating a worksheet with these udfs from my vsto addin. However, this does not seem very straightforward. – Buki May 01 '12 at 20:30
  • Hey I guess what I meant was that you can write a COM visible wrapper that delegates calls to that dll. The COM visible wrapper will be returned by RequestComAddinAutomationService(), which is a function in your ThisAddin class. –  May 01 '12 at 21:12
0

It is harder than it seems to combine VSTO and Automation in Excel. You may find my blog post helpful:

Communicating Between VSTO and UDF's in Excel

Kendall Frey
  • 43,130
  • 20
  • 110
  • 148
  • Thanks Kendall. I saw this blog post but it does not provide information on what i am trying to do. The automation udf addin is already available in excel, i just need it to be active by the time I am populating the worksheet with functions. I dont have the luxury of rewriting the automation addin. – Buki May 01 '12 at 20:26
  • Are you saying you depend on the order addins are loaded? That sounds like a bad idea. – Kendall Frey May 01 '12 at 20:30
  • No i dont depend on order of loading. The automation addin needs to be "active" in the Start -> excel addins menu the first time i try to populate a sheet with functions programmatically from my vsto addin otherwise i get the #NAME? error. This population is triggered by a button click event. For some reason, however the automation addin is not active at this point and only typing a udf from it into a cell by hand activates eg =udf(.....). If i try to programmatically populate worksheet after typing udf by hand it works fine and i can see that the automation addin is active . – Buki May 01 '12 at 21:55
  • I see what you mean. I'm not sure how to help, though. Sorry :( – Kendall Frey May 01 '12 at 21:57
0

Just need to add String Value to the following registry key and you are good.

For Office 2007

Find regkey, HKEY_CURRENT_USER\SOftware\Microsoft\Office\12.0\Excel\Options, then create string value, where name = OPEN, value = /A "YOUR ADDIN NAME HERE" (quotes need to be included as well.)

Note that for the first addin, value name should be called OPEN, for the second one and onwards, use OPEN1, OPEN2, ... etc.

For Office 2010

Just replace 12.0 with 14.0 in the above regkey path, the rest are all the same.

Check out below article on MSDN, which will also help you a lot.

http://support.microsoft.com/kb/291392

woodykiddy
  • 6,074
  • 16
  • 59
  • 100
0

Looks like this is a bug specific to VSTO. I converted my addin to a COM addin and was able to use the automation addin from code after that. My team has sent the issue to microsoft so we'll see what they say.

Buki
  • 21
  • 1
  • 3
  • Do you have the link to the bug report, we have the exact same issue :( and couldn't find a solution – Reza Jul 27 '12 at 17:37