troubleshooting Question

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

Avatar of e_livesay
e_livesayFlag for United States of America asked on
C#Visual Basic.NETMicrosoft ExcelMicrosoft Office.NET Programming
4 Comments1 Solution894 ViewsLast Modified:
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();
}

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

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

+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;
   }
}

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

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?
ASKER CERTIFIED SOLUTION
ste5an
Senior Developer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros