Get reference to Excel applications using .net and C#

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

sclarke2010

New Member
Joined
May 17, 2010
Messages
14
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

MrExcel MVP, Like totally RAD man
Joined
Feb 28, 2008
Messages
3,497
Office Version
365, 2016
Platform
Windows
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...


Rich (BB code):
using System.Diagnostics;
using System.Runtime.InteropServices;
//reference to Excel object library required
using Excel = Microsoft.Office.Interop.Excel;

Two methods:
Rich (BB code):
     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:

Rich (BB code):
   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!");
   }
 
Last edited:

sclarke2010

New Member
Joined
May 17, 2010
Messages
14
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

New Member
Joined
May 17, 2010
Messages
14
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

MrExcel MVP, Like totally RAD man
Joined
Feb 28, 2008
Messages
3,497
Office Version
365, 2016
Platform
Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,101,904
Messages
5,483,646
Members
407,399
Latest member
Rakeforms

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top