e_livesay
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.
This is the C# class that contains the routine mCount() which calls the VBA routine.
This is the VBA code.
+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.
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.
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?
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
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?
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();
//}
}
ASKER
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:
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;
}
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
But as Excel is a classical external, unmanaged resource, you should use the Dispose pattern and IDisposable interface. So that you can simply use
Open in new window