Avatar of e_livesay
e_livesay
Flag 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?
C#Visual Basic.NETMicrosoft ExcelMicrosoft Office.NET Programming

Avatar of undefined
Last Comment
ste5an

8/22/2022 - Mon