starting excel application with addins from c# application

Question

I have a c# app and it needs to create an excel application & then open a workbook. The issue is I need a Bloomberg addinn to be loaded when excel opens. The only way I have found is in this post working example.

This indeed starts excel and able to use the Bloomberg functions. However I was wondering if there was a way to cast myXl to xlApp where xlApp is of type Microsoft.Office.Interop.Excel.Application?

var myXl = Process.Start("excel.exe");

The reason being is I have a library which has some useful function I wish to make use of but it needs an a parameter of type Microsoft.Office.Interop.Excel.Application. How do I do this?

Answer 1

You can automate Excel from an external application. See How to automate Microsoft Excel from Microsoft Visual C#.NET and C# app automates Excel (CSAutomateExcel) for more information.

The Application class provides the following properties for accessing add-ins:

• AddIns - returns an AddIns collection that represents all the add-ins listed in the Add-Ins dialog box (Add-Ins command on the Developer tab); XLL add-ins.
• COMAddIns - returns the COMAddIns collection for Microsoft Excel, which represents the currently installed COM add-ins.

So, if you need to be sure that a COM add-in is enabled you need to use the ComAddins property of the Application class.

Answer 2

You can use the following code:

This will start Excel, then go through all Workbooks registered in the Running Object Table to find the one that runs in the process just started. To do this, it get the process id of the window handle of the Workbook and compares it to the id of the process just started.

This looking in the Running Object Table is repeated several times with waiting periods in between because Excel might need some time to register with the ROT after it was started. You may need to increase maxAttempts and waitTimeMS on slower computers.

If the correct Workbook is found, it is returned. In the sample, I will write "hello world" into the first cell of the Excel Application instance.

private void button1_Click(object sender, EventArgs e)
{
     Microsoft.Office.Interop.Excel.Application excelApplication = StartExcel();

    if (excelApplication != null)
    {
        excelApplication.ActiveCell.Value = "Hello World";
    }
}

[DllImport("user32.dll", SetLastError = true)]
static extern uint GetWindowThreadProcessId(IntPtr hWnd, out uint processId);

[DllImport("ole32.dll")]
private static extern int GetRunningObjectTable(int reserved, out IRunningObjectTable prot);

private Microsoft.Office.Interop.Excel.Application StartExcel()
{
    // Maximum number of attempts to look for started Excel Application
    const int maxAttempts = 3;
    // Number of milliseconds to wait between attempts to look for started Excel Application
    const int waitTimeMS = 200;

    Microsoft.Office.Interop.Excel.Application result = null;

    // Start Excel
    var process = Process.Start("Excel.exe");
    process.WaitForInputIdle();

    // Try to find started Excel Application

    int currentAttempt = 1;

    while ((result == null) && (currentAttempt <= maxAttempts))
    {
        // Wait between attempts 
        if(currentAttempt != 1)
        {
            Thread.Sleep(waitTimeMS);
        }

        // List all running Excel automation objects and find the one with the same process id
        IRunningObjectTable lRunningObjectTable = null;
        IEnumMoniker lMonikerList = null;

        try
        {
            // Query Running Object Table 
            if (GetRunningObjectTable(0, out lRunningObjectTable) == 0 && lRunningObjectTable != null)
            {

                // List Monikers
                lRunningObjectTable.EnumRunning(out lMonikerList);

                // Start Enumeration
                lMonikerList.Reset();

                // Array used for enumerating Monikers
                IMoniker[] lMonikerContainer = new IMoniker[1];

                IntPtr lPointerFetchedMonikers = IntPtr.Zero;

                // foreach Moniker
                while (lMonikerList.Next(1, lMonikerContainer, lPointerFetchedMonikers) == 0)
                {
                    object lComObject;
                    lRunningObjectTable.GetObject(lMonikerContainer[0], out lComObject);

                    // Check the object is an Excel workbook
                    if (lComObject is Microsoft.Office.Interop.Excel.Workbook)
                    {
                        Microsoft.Office.Interop.Excel.Workbook lExcelWorkbook = (Microsoft.Office.Interop.Excel.Workbook)lComObject;

                        // Get the Process ID for the Window Handle 
                        uint processId;
                        GetWindowThreadProcessId(new IntPtr(lExcelWorkbook.Application.Hwnd), out processId);

                        if (processId == process.Id)
                        {
                            // Correct automation object found, return Application
                            result = lExcelWorkbook.Application;
                            break;
                        }
                    }
                }
            }
        }
        finally
        {
            // Release ressources
            if (lRunningObjectTable != null) Marshal.ReleaseComObject(lRunningObjectTable);
            if (lMonikerList != null) Marshal.ReleaseComObject(lMonikerList);
        }

        currentAttempt++;
    }


    return result;
}