Get reference to Excel applications using .net and C#
Page 2 of 2 FirstFirst 12
Results 11 to 17 of 17

Thread: Get reference to Excel applications using .net and C#
Thanks Thanks: 0 Likes Likes: 0

  1. #11
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Get reference to Excel applications using .net and C#

    Are you allowed or willing to use unmanaged code?

  2. #12
    New Member
    Join Date
    May 2010
    Location
    Cork, Ireland
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Get reference to Excel applications using .net and C#

    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

  3. #13
    MrExcel MVP
    Like totally RAD man
    Colin Legg's Avatar
    Join Date
    Feb 2008
    Location
    UK
    Posts
    3,497
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Get reference to Excel applications using .net and C#

    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...


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

    Two methods:
    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:

    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 by Colin Legg; May 23rd, 2010 at 02:45 PM.

  4. #14
    New Member
    Join Date
    May 2010
    Location
    Cork, Ireland
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Get reference to Excel applications using .net and C#

    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

  5. #15
    New Member
    Join Date
    May 2010
    Location
    Cork, Ireland
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Get reference to Excel applications using .net and C#

    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
    {

    classExcelproj
    {
    public Excel.Application oExcelApp = null;
    publicWorkbooks objBooks = null;
    public_Workbook objBook = null;

    publicvoid ExcelClass()
    {
    try
    {
    conststring FilePath = @"C:\TEMP\Test\";
    constint 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)
    {
    returnnull;
    }
    }

    Process GetExcelProcess(Excel.Application xlApp)
    {
    Process[] excelProcesses = Process.GetProcessesByName("Excel");
    foreach (Process excelProcess in excelProcesses)
    {
    if (xlApp.Hwnd == excelProcess.MainWindowHandle.ToInt32())
    {
    return excelProcess;
    }
    }
    thrownewInvalidOperationException(
    "Unexplained operation of the 'Process' class: the Excel process could not be returned.");
    }
    }
    }


  6. #16
    MrExcel MVP
    Like totally RAD man
    Colin Legg's Avatar
    Join Date
    Feb 2008
    Location
    UK
    Posts
    3,497
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Get reference to Excel applications using .net and C#

    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.

  7. #17
    MrExcel MVP Zack Barresse's Avatar
    Join Date
    Dec 2003
    Location
    Oregon, USA
    Posts
    10,724
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Get reference to Excel applications using .net and C#

    Moved to appropriate forum.
    Regards,
    Zack Barresse
    My Book on Excel Tables
    (If you would like comments in any code, please say so.)

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •