Running an excel application with add-ons from a C # application

Question

I have a C # application and it needs to create an excel application and then open the book. The problem is that I need to add Bloomberg addinn when Excel opens. The only way I found is the desktop.

It really is starting to excel and can use Bloomberg features. However, I was wondering if there is a way to pass myXl to xlApp, where xlApp is of type Microsoft.Office.Interop.Excel.Application?

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

The reason is because I have a library that has a useful function that I want to use, but it needs a parameter like Microsoft.Office.Interop.Excel.Application. How to do it?

Answer 1

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

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

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

So, if you need to make sure that the 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, and then look through all the books registered in the desktop table to find the one that the process that just started is starting. To do this, it obtains the process identifier of the window handle of the workbook and compares it with the identifier of the process that has just begun.

This search in the Running Object Table is repeated several times with waiting periods between them, because Excel may take some time to register with the ROT after it starts. You may need to increase maxAttempts and waitTimeMS on slower computers.

If the found workbook is found, it is returned. In the example, I will write "hello world" in the first cell of the instance of the Excel application.

 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; } 

Answer 3

Add a link to the Excel library and instead of var myXL you can create an instance of a workbook object

 Excel.Application myXl = New Excel.Application(); 

Then you just need to manually load the add-in.

  foreach (Excel.AddIn item in myXl.AddIns) { if (item.Name.Equals("BLOOMBERG ADDIN NAME")) { item.Installed = true; } }