# Get reference to Excel applications using .net and C#



## sclarke2010 (May 17, 2010)

Hi, I have been trying to get a reference to Excel applications using  oExcelApp = (Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");  in C#.  This is so that I can use code to automatically save any open workbooks & close the first Excel application, and then do the same for the next Excel application, etc.  However unlike the other Office apps (Word, etc) the reference to Excel seems to stay permanently (until the code is finished) ie I can't get the reference to move on to the next Excel application. I have tried killing the Excel app after the first iteration, using threads, etc. Any ideas/thoughts to why this may be happenning?


----------



## Norie (May 17, 2010)

Why are there multiple instances of Excel open in the first place?

I've worked a little with automating Excel via C# and let's just say it was a bit quirky.

It took me about a week to write a simple program to create a new worksheet in an existing workbook and list the shapes.


----------



## Legacy 98055 (May 17, 2010)

I wonder if this is a garbage collection issue?  Have you forced disposed?


----------



## sclarke2010 (May 17, 2010)

Hi Norie & Tom,
Thanks for getting back to me.

Norie - yes it seems quirky compared to other Office apps. The reason is in case someone has more than one Excel app open.

Tom - yes I have tried forcing disposal and releasing the com object, as follows: 
oExcelApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcelApp);
GC.GetTotalMemory(false);
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.GetTotalMemory(true);


----------



## Norie (May 17, 2010)

Stephen

I wasn't referring to Excel being quirky, it was C# I meant.

I don't think the behaviour you are seeing is down to Excel itself.

It could be for some other reason - automating any application isn't always straightforward.

By the way I'm still wondering why you have multiple instances of Excel open.

One thing I've found in the past when automating is that if you don't reference things properly you can find yourself with 'ghost' instances.


----------



## Colin Legg (May 17, 2010)

> Tom - yes I have tried forcing disposal and releasing the com object, as follows:
> oExcelApp.Quit();
> System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcelApp);
> GC.GetTotalMemory(false);
> ...


 

The prescribed cleanup I use is like this:


```
GC.Collect();                 
GC.WaitForPendingFinalizers();
GC.Collect();                  
GC.WaitForPendingFinalizers();
 
oExcelApp.Quit();
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(oExcelApp);
```
Note that GC.Collect() and GC.WaitForPendingFinalizers() are called twice.

If you have a reference to any of the Excel workbooks (which you are saving) then additional cleanup will be required, ie Close workbook then call Marshal.FinalReleaseComObject(oExcelWkb) prior to closing the Excel application.


----------



## sclarke2010 (May 17, 2010)

Thanks Colin.  I have tried as you suggested, but the instance of Excel is still showing in Task manager after all these GC and Quit commands.  I also tried process.kill and this removed it, but got the error 'The RPC server is unavailable' when it tried to get the reference of the next Excel application. Any further ideas?


----------



## Colin Legg (May 17, 2010)

Hi,

Please can you post the full code? Which version of C# are you using?

I don't have VS to hand but I might spot something; if I can't see anything I can have a play later when I get home from work.


----------



## Legacy 98055 (May 17, 2010)

Excel does not register itself in the ROT.  I wonder if this is causing the problem?  Workbooks do register and I have found myself using the workbook object as the root object and simply referring to the parent when I need a reference to the application.


----------



## sclarke2010 (May 18, 2010)

Thanks Tom and Colin for your replies.

Colin - I have posted my code so far as below.  Am using Visual C# 2008 and Visual Studio 2008.

Tom - yes this is something that I have come across before, and may well be the case. I have tried to get it to register the apps in the past ie once the first one is closed to move on to the next one, but wasn't able to get it to move on.  This was mainly because the first Excel app still existed in memory and showed up on task manager even when the application is quit. How would you recommend changing the code below / suggestions to refer to the workbooks in the first Excel application and then referring to workbooks on the next Excel application?


using System;
using System.Collections.Generic;
using System.Runtime.InteropServices;
using System.Diagnostics;
using System.ComponentModel;
using Microsoft.Office.Interop.Excel;
using Excel = Microsoft.Office.Interop.Excel;
using System.Windows.Forms;
 
namespace Officedocs
{
class Excelproj
{
public static void ExcelClass()
{

Excel.Application oExcelApp = null;

 
try
{
//To get reference to the Excel app
oExcelApp = (Excel.Application)System.Runtime
.InteropServices.Marshal.GetActiveObject("Excel.Application");

oExcelApp.DisplayAlerts = false; //don't display updates
 
Process[] processlist = Process.GetProcessesByName("Excel"); //Shows number of running Excel apps
foreach (Process theprocess in processlist) //foreach Excel app running
{
 
if (oExcelApp.Workbooks.Count >= 0) //for worbooks in each Excel app
{
foreach (Excel.Workbook wkb in oExcelApp.Application.Workbooks)
{
 
//Save files using their own names in the specified folder
Object oSaveAsFileExcel1 = wkb.Name;
 
//Save each workbook
wkb.SaveAs(oSaveAsFileExcel1, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Excel.XlSaveAsAccessMode.xlNoChange, XlSaveConflictResolution.xlLocalSessionChanges,
true, Type.Missing, Type.Missing, Type.Missing);

wkb.Close(true, null, null);
 
//Release the wbk object
Marshal.FinalReleaseComObject(wkb); //Release the Excel wkb object
}
 
//Close workbooks 
oExcelApp.Workbooks.Close();
}

GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();

oExcelApp.Quit();

System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcelApp);
}
return;

}
catch //(Exception x)
{ }
}
}
}


----------



## sclarke2010 (May 17, 2010)

Hi, I have been trying to get a reference to Excel applications using  oExcelApp = (Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");  in C#.  This is so that I can use code to automatically save any open workbooks & close the first Excel application, and then do the same for the next Excel application, etc.  However unlike the other Office apps (Word, etc) the reference to Excel seems to stay permanently (until the code is finished) ie I can't get the reference to move on to the next Excel application. I have tried killing the Excel app after the first iteration, using threads, etc. Any ideas/thoughts to why this may be happenning?


----------



## Legacy 98055 (May 18, 2010)

Are you allowed or willing to use unmanaged code?


----------



## sclarke2010 (May 18, 2010)

Hi Tom,

I would prefer managed, but if this only works in unmanaged code then this is fine too.

Do you have some change or suggestion in mind?

Stephen


----------



## Colin Legg (May 23, 2010)

Hi Stephen,

This code (written in C# 4.0) works for me: it closes all Excel instances and saves the open workbooks to my desktop. Mike_R suggested using Process.WaitForExit(), so full credit to him for the idea. If you're using Visual Studio 2008 then you'll have to make some tweaks to get it going...



```
using System.Diagnostics;
using System.Runtime.InteropServices;
//reference to Excel object library required
using Excel = Microsoft.Office.Interop.Excel;
```
 

Two methods:

```
Excel.Application GetExcelObject()
     {
         try
         {
             return (Excel.Application)Marshal.GetActiveObject("Excel.Application");
         }
         catch (Exception)
         {
             return null;
         }
     }
 
     Process GetExcelProcess(Excel.Application xlApp)
     {
         Process[] excelProcesses = Process.GetProcessesByName("Excel");
         foreach (Process excelProcess in excelProcesses)
         {
             if (xlApp.Hwnd == excelProcess.MainWindowHandle.ToInt32())
             {
                 return excelProcess;
             }
         }
         throw new InvalidOperationException(
            "Unexplained operation of the 'Process' class: the Excel process could not be returned.");
     }
```
 
Then I used a button on a form to execute the procedure:


```
private void button1_Click(object sender, EventArgs e)
   {
       const string FilePath = @"C:\Users\Colin\Desktop\";
       const int MaxWait = 60000;
 
       Excel.Application xlApp = GetExcelObject(); 
 
       while (xlApp != null)
       {
           xlApp.DisplayAlerts = false;
           xlApp.EnableEvents = false;
 
           //save and close each workbook
           foreach (Excel.Workbook xlWkb in xlApp.Workbooks)
           {
               string fileExtension;
 
              switch (xlWkb.FileFormat)
               {
                   case Excel.XlFileFormat.xlOpenXMLWorkbook:
                       fileExtension = ".xlsx";
                       break;
                   case Excel.XlFileFormat.xlOpenXMLWorkbookMacroEnabled:
                       fileExtension = ".xlsm";
                       break;
                   default:
                       fileExtension = ".xls";
                       break;
               }
              string fileFullName =
                   FilePath + xlWkb.Name + DateTime.Now.ToString("yy MM dd HHmmss fff") + fileExtension;
               xlWkb.SaveAs(Filename: fileFullName, FileFormat: xlWkb.FileFormat);
               xlWkb.Close(SaveChanges: false);
               Marshal.FinalReleaseComObject(xlWkb);
           }
 
           //Find the currently referenced Excel process so we can be sure when it has been properly killed
           Process xlProcess = GetExcelProcess(xlApp);
 
          // Only need to call GC.Collect() and GC.WaitForPendingFinalizers() once unless using VSTO:
           GC.Collect();
           GC.WaitForPendingFinalizers();
           xlApp.Quit();
           Marshal.FinalReleaseComObject(xlApp);
 
          //wait for the process to completely close before moving on
           xlProcess.WaitForExit(MaxWait);
 
           // Get next Excel.Application object (if available).
           xlApp = GetExcelObject();
       }
       MessageBox.Show("Done!");
   }
```


----------



## sclarke2010 (May 28, 2010)

Thanks for this Colin and Mike.  I am away for a few days with intermittent internet access, but will certainly try this when I get back to the office and let you know either way.

Thanks again,

Stephen


----------



## sclarke2010 (Jun 10, 2010)

Hi Colin,

I have now modified the code you sent on in order to work with VS2008 and C#2008, and so that I can call this class in one block so as no user intervention is required.

The code looks fine to me, however when it gets to the end it skips by the second Excel app, as it still hasn't released the first app (even using the WaitForExit(MaxWait) part).  I can see this on the task manager.  It only releases the originally referenced Excel application properly when the program is stopped, which is too late.  I have included the updated code based on your suggestions, so perhaps you could try this to see if it works for you.

I also tried _xlProcess.Kill();_ rather than _xlProcess.WaitForExit(MaxWait);_ but that didn't make a difference.

Also I wonder if this problem with not releasing the Excel application can be set using some other setting - just a thought?

Regards,

Stephen


using System;
using System.Collections.Generic;
using System.Runtime.InteropServices;
using System.Diagnostics;
using System.ComponentModel;
using Microsoft.Office.Interop.Excel;
using Excel = Microsoft.Office.Interop.Excel;
using System.Windows.Forms;
 
namespace Officedocs
{
 
class Excelproj
{
public Excel.Application oExcelApp = null;
public Workbooks objBooks = null;
public _Workbook objBook = null;
 
public void ExcelClass()
{
try
{
const string FilePath = @"C:\TEMP\Test\";
const int MaxWait = 6000;
Excel.Application xlApp = GetExcelObject();
 
while (xlApp != null)
{
xlApp.DisplayAlerts = false;
xlApp.EnableEvents = false;
 
//save and close each workbook
foreach (Excel.Workbook xlWkb in xlApp.Workbooks)
{
string fileExtension;
switch (xlWkb.FileFormat)
{
case Excel.XlFileFormat.xlOpenXMLWorkbook:
fileExtension = ".xlsx";
break;
case Excel.XlFileFormat.xlOpenXMLWorkbookMacroEnabled:
fileExtension = ".xlsm";
break;
default:
fileExtension = ".xls";
break;
}
string fileFullName = FilePath + xlWkb.Name + DateTime.Now.ToString("yy MM dd HHmmss fff") + fileExtension;
xlWkb.SaveAs(fileFullName, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Excel.XlSaveAsAccessMode.xlNoChange, XlSaveConflictResolution.xlLocalSessionChanges,
true, Type.Missing, Type.Missing, Type.Missing);
xlWkb.Close(true, null, null);
Marshal.FinalReleaseComObject(xlWkb);

}
//Find the currently referenced Excel process so we can be sure when it has been properly killed
Process xlProcess = GetExcelProcess(xlApp);
// Only need to call GC.Collect() and GC.WaitForPendingFinalizers() once unless using VSTO:
GC.Collect();
GC.WaitForPendingFinalizers();
xlApp.Quit();
Marshal.FinalReleaseComObject(xlApp);
 
//wait for the process to completely close before moving on
xlProcess.WaitForExit(MaxWait);
 
// Get next Excel.Application object (if available).
xlApp = GetExcelObject();
}
//MessageBox.Show("Done!");
}
 
catch { }
finally { }
}

Excel.Application GetExcelObject()
{
try
{
return (Excel.Application)Marshal.GetActiveObject("Excel.Application");
}
catch (Exception)
{
return null;
}
}
 
Process GetExcelProcess(Excel.Application xlApp)
{
Process[] excelProcesses = Process.GetProcessesByName("Excel");
foreach (Process excelProcess in excelProcesses)
{
if (xlApp.Hwnd == excelProcess.MainWindowHandle.ToInt32())
{
return excelProcess;
}
}
throw new InvalidOperationException(
"Unexplained operation of the 'Process' class: the Excel process could not be returned.");
}
}
}


----------



## Colin Legg (Jun 12, 2010)

Hi Stephen,

The 'good' news is your code works fine for me.


> I wonder if this problem with not releasing the Excel application can be set using some other setting - just a thought?


Yes, there are other factors. The next thing I suggest you try is running your code on instances of Excel where no add-ins are running. If it works then you'll know that one of the add-ins is a problem and you can install them one at a time until the problem resurfacess, and then you'll know which one the culprit is.


----------



## Zack Barresse (Jun 12, 2010)

Moved to appropriate forum.


----------



## Tom Black (Aug 21, 2020)

I use ZetExcel.


----------



## Tom Black (Aug 21, 2020)

I use ZetExcel. It is the fastest Excel SDK for .net applications.


----------

