CODEWITHSUNDEEP
excelautomationvstouser-defined-functions
Don B
Don B

Reputation: 53

Excel UDF not available when opening Excel via automation

I’m having a problem getting an Excel UDF class to be available in Excel when I launch Excel using automation. I have an add-in I’m writing that needs to work when Excel is launched from a third-party application.

It is my understanding that this is by design, but I need to have a way to make my UDFs available to users when Excel is opened via automation.

I created a VSTO addin and UDF class for Excel 2007. I copied the code and followed the instructions from this web page exactly as written, except I replaced the GUIDs as instructed. http://csharpramblings.blogspot.ca/2011/09/communicating-between-vsto-and-udfs-in.html

When I open Excel, I see my new add-in and the UDF shows as available in the list of functions (Insert Function). So I know it’s working.

I then created an application that uses Excel automation to open Excel.
It is a Windows forms application, and I put a button on Form1. I added a reference to Microsoft.Office.Interop.Excel (version 1.6.0.0) which resolves (on my machine) to C:\Windows\assembly\GAC\Microsoft.Office.Interop.Excel\12.0.0.0__71e9bce111e9429c\Microsoft.Office.Interop.Excel.dll

I got the following code from http://support.microsoft.com/kb/302084, although I removed some of the code that puts data into the worksheet’s cells because I didn’t need it – I just wanted an example of opening Excel using automation.

The C# code for Form1 for my sample program:

using System;
using System.Reflection;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
namespace ExcelAutomationTest
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        private void button1_Click(object sender, System.EventArgs e)
        {
            Excel.Application oXL;
            Excel._Workbook oWB;
            Excel._Worksheet oSheet;
            Excel.Range oRng;
            try
            {
                //Start Excel and get Application object.
                oXL = new Excel.Application();
                oXL.Visible = true;
                //Get a new workbook.
                oWB = (Excel._Workbook)(oXL.Workbooks.Add(Missing.Value));
                oSheet = (Excel._Worksheet)oWB.ActiveSheet;
                //Add table headers going cell by cell.
                oSheet.Cells[1, 1] = "First Name";
                oSheet.Cells[1, 2] = "Last Name";
                oSheet.Cells[1, 3] = "Full Name";
                oSheet.Cells[1, 4] = "Salary";
                //Make sure Excel is visible and give the user control
                //of Microsoft Excel's lifetime.
                oXL.Visible = true;
                oXL.UserControl = true;
            }
            catch (Exception theException)
            {
                String errorMessage;
                errorMessage = "Error: ";
                errorMessage = String.Concat(errorMessage, theException.Message);
                errorMessage = String.Concat(errorMessage, " Line: ");
                errorMessage = String.Concat(errorMessage, theException.Source);
                MessageBox.Show(errorMessage, "Error");
            }
        }
    }

Build the windows forms app that automates Excel. Run the app. It will start Excel. Go to the "Formulas" tab and click "Insert Function". View "All" functions. On my machine, MYINT is not in the list.

Is it possible to make the MYINT function available when Excel opens using automation? One person suggested I could use the Excel.Application object in the VSTO ThisAddIn_Startup() to load the UDF, but I was unable to find a sample of how to do that, and the person helping me didn’t know.

I asked on the MSDN Excel and VSTO forums for help and wasn’t able to get a clear picture of what to do next. There are some similar questions, but nothing quite the same, around StackOverflow. Does anyone have any ideas?

Upvotes: 1

Views: 711

Answers (2)

user1863359
user1863359

Reputation: 317

when Excel file is opened from a c# code, all additional add-in will not be loaded with (this is why you didn't found your UDF function in "Formulas" tab), so you have to make your code call add-in that you need :

Upvotes: 1

Don B
Don B

Reputation: 53

I followed the advice at Techiella's blog and it appears to have solved the problem.

My C# code for this is as follows - I put it in ThisAddIn_Startup:

foreach (AddIn addin in ThisApplication.AddIns)
{
  if (addin.Title == "MyUdf.Functions")
  {
    addin.Installed = false;
    addin.Installed = true; 
    break; 
  }
}

Don

Upvotes: 2

Related Questions