Link to home
Start Free TrialLog in
Avatar of e_livesay
e_livesayFlag for United States of America

asked on

How can I use a C# class constructor and destructor to deal with Excel interactions?

I have C# code that is successfully running a VBA function written in Excel.  To do this it opens Excel, runs the function and then closes Excel.  Watching in the Task Manager I can see EXCEL.EXE open and then close.

My question is about rearranging the C# code to better handle the interaction with Excel. Right now the C# code 'gets ahold' of Excel, calls the VBA routine and then 'lets go' of Excel.  I would like to put code that 'gets ahold' of Excel into a class constructor and the code that 'lets go' into a class destructor.  The constructor code seems to work fine, the destructor code does not (an error is generated by Visual Studio 2012 - see below) and every time I call the VBA routine a new copy of EXCEL.EXE pops-up in the Task Manager.

+What I have now+
Below is what I have now.  Each time I click the 'Misc' test button whose Event Handler (cmdMisc_Click(...)) is shown below I can watch EXCEL.EXE pop-up and go away in the Task Manager.  

This is the C# Event Handler for a button on a Form that I'm using for testing.
private void cmdMisc_Click              (object sender, EventArgs e)
{
   rhsVBA       cs       = new rhsVBA();
   int          n        = cs.mCount();
}

Open in new window


This is the C# class that contains the routine mCount() which calls the VBA routine.
public class rhsVBA
{
   //... Other routines ...//
   
   public int            mCount()
   {
      Microsoft.Office.Interop.Excel.Application  xlApp;
      Workbook                                    wb;

      // Get ahold of the application and the workbook.
      xlApp         = new Microsoft.Office.Interop.Excel.Application();
      xlApp.Visible = false;
      wb            = xlApp.Workbooks.Open("C:\\MyExcelFile.xlsm");

      // Call the Excel function
      int N = xlApp.Run("mCount");

      // Let go of Excel
      xlApp.Quit();
      System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
      GC.Collect();
      GC.WaitForPendingFinalizers();

      // Return the data
      return N;
   }
}

Open in new window


This is the VBA code.
Option Explicit
Option Base 0
' ... Variable definitions ...
' ... Other routines ...

Function mCount() As Integer

    Dim m() As Double
    
    m = GetValuesFromExcel("B4")
    
    mCount = UBound(m) + 1

End Function

Function GetValuesFromExcel(startCell As String) As Double()

    Dim x() As Double
    Range(startCell).Select
    Dim i As Integer
    i = 0
    Do Until IsEmpty(ActiveCell)
    
        ReDim Preserve x(i)
    
        x(i) = ActiveCell.Value
    
        ActiveCell.Offset(1, 0).Select
        
        i = i + 1
    Loop
    
    GetValuesFromExcel = x

End Function
' ... Other routines ...

Open in new window


+What I would like to do+
What I would like to do is modify the C# rhsVBA class by adding a constructor and a destructor as shown below.  The Event Handler and the VBA code shown above would be unaltered.

public class rhsVBA
{
   Microsoft.Office.Interop.Excel.Application  xlApp;
   Workbook                                    wb;

   public rhsVBA()
   {
      // Get ahold of the application and the workbook.
      xlApp         = new Microsoft.Office.Interop.Excel.Application();
      xlApp.Visible = false;
      wb            = xlApp.Workbooks.Open("C:\\MyExcelFile.xlsm");
   }
   
   ~rhsVBA()
   {
      // Let go of Excel
      xlApp.Quit();
      System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
      GC.Collect();
      GC.WaitForPendingFinalizers();
   }

   //... Other routines ...//
   
   public int            mCount()
   {
      // Call the Excel function
      int N = xlApp.Run("mCount");
 
      // Return the data
      return N;
   }
}

Open in new window


The above code compiles and runs.  However, each time I click the 'Misc' test button (whose Event Handler is cmdMisc_Click(...)) I watch a new instance of EXCEL.EXE pop-up in the Task Manager.  Clicking the 'Misc' test button three times leads to three instances of EXCEL.EXE to show up in the Task Manager and not go away.  It was my impression that at the end of the 'Misc' test button Event Handler the destructor for my instance of rhsVBA would be called, releasing Excel.  This doesn't seem to be the case.

private void cmdMisc_Click              (object sender, EventArgs e)
{
   rhsVBA       cs       = new rhsVBA();
   int          n        = cs.mCount();
   // I thought that the destructor for cs would be called here.
}

Open in new window


When I click the red X at the upper-right of my test Form I end up in the debugger with xlApp.Quit() highlighted and an error message displayed:
InvalidComObjectException was unhandled.
COM object that has been separated from its underlying RCW cannot be used.

I see no reason for the Excel setup/disconnect code to be in the routine mCount().  There are other routines that are going to be called in loops and they definitiely don't need the Excel setup/disconnect code in them.

How can I call the Excel setup and Excel disconnect code only once?
Avatar of ste5an
ste5an
Flag of Germany image

Well, the problem is that the destructor is called most time implicitly by GC.

But as Excel is a classical external, unmanaged resource, you should use the Dispose pattern and IDisposable interface. So that you can simply use

using(rhsVBA excelAutomation = new rhsVBA)
{
    // Invoke your code here.
}

Open in new window

Avatar of e_livesay

ASKER

I removed the destructor from rhsVBA and modified this class so that it implements the IDisposable interface according to the Dispose pattern. The code compiles, runs and does not give an error when I exit the program.  However, I don't know how to get the SafeHandle of the Excel Application that I've started and thus don't see how to release it.  As a result I end up with one instance of EXCEL in the Task Manager for every time that I clicked the 'Misc' test button (whose Event Handler is cmdMisc_Click(...)) .  Additionally, my breakpoints set in Dispose() or Dispose(bool disposing) are never hit when I am running the debugger, even when I exit the program by clicking the red X in the upper-right hand corner of the main window.

How do I get the SafeHandle for the Excel application?

public class rhsVBA : IDisposable
{
   Microsoft.Office.Interop.Excel.Application  xlApp;
   Workbook                                                         wb;
   private SafeHandle                                         resource;    // How do I get this?
   public rhsVBA()
   {
      // Get ahold of the application and the workbook.
      xlApp           = new Microsoft.Office.Interop.Excel.Application();
      xlApp.Visible   = false;
      wb              = xlApp.Workbooks.Open("C:\\Conoco\\NumericalRecipesInCExamples\\Odeint_SimpleHarmonicOscillators_Coupled\\ODEspecification_VBA\\ODEspecification_VBA.xlsm");
   }
   public void Dispose()
   {
      Dispose(true);
      GC.SuppressFinalize(this);
   }
   public virtual void Dispose(bool disposing)
   {
      if (disposing)
      {
         if (resource != null) resource.Dispose();   // resource is always null because I don't know how to set it.
      }
   }
   //~rhsVBA()        // Commented-out the destructor
   //{
  //   xlApp.Quit();
  //   System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
  //   GC.Collect();
  //   GC.WaitForPendingFinalizers();
  //}
}

Open in new window

While looking around for ways to get a SafeHandle for my instance of Excel I came across some stuff about using IntPtr (see this article).  However, before and after the call to GetWindowThreadProcessID(...) both resource (which is now an IntPtr) and the newly added variable processID are 0.

My class rhsVBA now looks like this:

public class rhsVBA : IDisposable
{
   [DllImport("user32.dll")]
   public static extern int GetWindowThreadProcessId(IntPtr handle, out uint threadid);

   [DllImport("kernel32", SetLastError = true)]
   internal extern static bool CloseHandle(IntPtr handle);

   Microsoft.Office.Interop.Excel.Application  xlApp;
   Workbook                                    wb;
   //private SafeHandle                          resource;
   private IntPtr                              resource;
   uint                                        processID;
   public rhsVBA()
   {
      // Get ahold of the application and the workbook.
      xlApp           = new Microsoft.Office.Interop.Excel.Application();
      xlApp.Visible   = false;
      wb              = xlApp.Workbooks.Open("C:\\Conoco\\NumericalRecipesInCExamples\\Odeint_SimpleHarmonicOscillators_Coupled\\ODEspecification_VBA\\ODEspecification_VBA.xlsm");

      GetWindowThreadProcessId(resource, out processID);
   }
   public void Dispose()
   {
      Dispose(true);
      GC.SuppressFinalize(this);
   }
   public virtual void Dispose(bool disposing)
   {
       if (disposing)
       {
          if (resource != IntPtr.Zero)
          {
            CloseHandle(resource);
         }
         //if (resource != null) resource.Dispose();
      }
   }
   //~rhsVBA()
   //{
   //   xlApp.Quit();
   //   System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
   //   GC.Collect();
   //   GC.WaitForPendingFinalizers();
   //} 

   //... Other routines ...//
   
   public int            mCount()
   {
      // Call the Excel function
      int N = xlApp.Run("mCount");
 
      // Return the data
      return N;
   }

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of ste5an
ste5an
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial