Get reference to Excel applications using .net and C#
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

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

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

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

    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?

  2. #2
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    75,109
    Post Thanks / Like
    Mentioned
    60 Post(s)
    Tagged
    6 Thread(s)

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

    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.
    If posting code please use code tags.

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

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

  4. #4
    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 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);

  5. #5
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    75,109
    Post Thanks / Like
    Mentioned
    60 Post(s)
    Tagged
    6 Thread(s)

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

    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.
    If posting code please use code tags.

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

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

    The prescribed cleanup I use is like this:

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

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

  8. #8
    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,

    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.

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

    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.

  10. #10
    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 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
    {
    classExcelproj
    {
    publicstaticvoid 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)
    { }
    }
    }
    }

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
  •