Solved

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

Posted on 2014-11-15
4
577 Views
Last Modified: 2014-12-08
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?
0
Comment
Question by:e_livesay
  • 2
  • 2
4 Comments
 
LVL 32

Expert Comment

by:ste5an
ID: 40445639
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

0
 

Author Comment

by:e_livesay
ID: 40454076
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

0
 

Author Comment

by:e_livesay
ID: 40454120
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

0
 
LVL 32

Accepted Solution

by:
ste5an earned 500 total points
ID: 40480323
There are some points you need to know: Office interop is classic COM. Thus there are a lot of objects involved, which you need to take care of.

Here's a simple example how you may do it (without any exception handling):

namespace WindowsFormsApplication1
{
    using System;
    using System.Runtime.InteropServices;
    using System.Windows.Forms;
    using Excel = Microsoft.Office.Interop.Excel;

    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void btnExcelAutomation_Click(object sender, EventArgs e)
        {
            using (ExcelAutomation ea = new ExcelAutomation())
            {
                ea.LoadWorkbook(@"C:\Temp\Book1.xlsm");
                MessageBox.Show("Waiting..");
            }

            MessageBox.Show("Done.");
        }
    }

    public class ExcelAutomation : IDisposable
    {
        private Excel.Application application = null;
        private Excel.Workbooks workbooks = null;
        private Excel.Workbook workbook = null;

        public ExcelAutomation()
        {
            this.application = new Excel.Application();
            this.workbooks = this.application.Workbooks;
        }

        public void Dispose()
        {
            if (this.workbook != null)
            {
                this.workbook.Close();
                Marshal.ReleaseComObject(this.workbook);
            }

            if (this.workbooks != null)
            {
                Marshal.ReleaseComObject(this.workbooks);
            }

            if (this.application != null)
            {
                this.application.Quit();
                Marshal.ReleaseComObject(this.application);
            }
        }

        public void LoadWorkbook(string fileName)
        {
            if (this.application != null)
            {
                this.workbook = this.workbooks.Open(fileName);
            }
        }

        public int? RunMacro(string macroName)
        {
            int? result = null;
            if (this.application != null)
            {
                result = this.application.Run(macroName);
            }

            return result;
        }
    }
}

Open in new window


The rule of thumb is: Whenever you use more than one dot in a statement, then you may have created a COM object. E.g.  take a look at the Workbooks part, When you would use this.workbook = this.application.Workbooks(fileName) then we would have a COM object which would keep Excel open as long our application runs. Verify this.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now