Excel Automation using C# - accessing the Cells[]

[YoelPlat]

Hi, I've written a simple COM object to allow me to automate Excel. In this
object, I have a method that will use getActiveObject to get the current
Excel Application which is opened to a workbook with a worksheet. I then
store the ActiveWorkbook and ActiveSheet inside variables oWB and oSheet
respectively. When I try to access oSheet.Cells[] it crashes. However, when
I create a new excel application and add a workbook, I can access
oSheet.Cells[] just fine and write whatever I want to the sheet.

I know that I've actually gotten ahold of the original Excel application
when I use getActiveObject because I can change the visibility property of
the application, as well as other properties.

The following is a small code segment that illustrates the problem. When I
remove the line oSheet.Cells[3,3] = 5; whichever macro that I call will
receive the return value. However, when that line is there, it receives a
bogus value and if I try displaying the return value as the return value of a
macro , i get #VALUE! in the cell.

Here's my code:

using System;
using System.Collections.Generic;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;


namespace excelTest
{
[ClassInterface(ClassInterfaceType.AutoDual)]
public class myBLP
{
public myBLP()
{
}

public double writeCell()
{
Excel.Application oExcelApp;
Excel.Workbook oWB;
Excel.Worksheet oSheet;

oExcelApp = (Excel.Application)
System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");

oWB = oExcelApp.ActiveWorkbook;
oSheet = (Excel.Worksheet) oWB.ActiveSheet;

oSheet.Cells[3,3] = "TEST";

oSheet = null;
oWB = null;
oExcelApp = null;

return 3;
}

 

[YoelPlat]

I've managed to fix the problem by using a Sub() to call my C# method. I am
then able to access to the cells. However, even with a Public Function() in
VBA, I can not call my C# method to access the cells[]. Any ideas? When you
call a Function in VBA that calls a .dll method, does the workbook lock up as
a security measure?