C# AddIn can't change cells in Excel

[Hippo Man]

I have a C# AddIn for Excel in which I get the Excel.Application object as follows:

private Excel.Application applicationObject;
public void OnConnection(object application, Extensibility.ext_ConnectMode connectMode, 
			 object addInInst, ref System.Array custom)
{
    applicationObject = (Excel.Application) application;
}

I can get all sorts of information about the spreadsheet via methods on applicationObject, but I am unable to change anything on the spreadsheet through this object. If I do this ...

applicationObject.calculateFull();

... or this ...

Excel.Worksheet ws = (Excel.Worksheet) applicationObject.Worksheets[1];
Excel.Range range  = (Excel.Range) ws.Cells;
int cc = ws.UsedRange.Columns.Count;
for (int c = 1; c <= cc; c++)
{
    range.set_Item(10, c, "test"); // row 10 starts out empty and unlocked
}

... or anything else which attempts to change the contents of the spreadsheet, I get an HRESULT 0x800A03EC exception.

Ive searched around the net, but I cant find anything which helps me get past this error. Some people have talked about localization as a possible cause of errors like this, but if I do the following before making the offending calls, I still get the same exception:

System.Threading.Thread thisThread =
  System.Threading.Thread.CurrentThread;
thisThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

Therefore, I dont think that localization is the issue.

Could it be that COM Interop doesnt permit me to change anything on the spreadsheet via that Excel.Application object? If so, is there any other way to accomplish this within a COM-Interop-based AddIn?

Thanks in advance.

 

[herilane]

No, there is nothing inherent in COM Interop that prohibits changing an Excel workbook. (If that was the case, that would make COM Interop rather useless.)

1) Check that you actually have an open workbook at that point.

2) Which line of code actually fails?
Are you able to get the worksheet object? Are you able to get the range object? Are you able to read cell values?

 

[Hippo Man]

Thank you very much for your reply ...

No, there is nothing inherent in COM Interop that prohibits changing an Excel workbook. (If that was the case, that would make COM Interop rather useless.)

1) Check that you actually have an open workbook at that point.

I indeed do. For example, from within my C# COM Interop code, I am able to retrieve any and all data from all cells on the worksheet.

2) Which line of code actually fails?

I gave two examples above of specific lines that fail. Look at my first message in this thread. I show excerpts of the code that I have written. The line applicationObject.calculateFull() fails. Also, the line range.set_Item(10,c,"test") fails in the same way. As I mentioned, both cause the HRESULT 0x800A03EC exception to be thrown.

Are you able to get the worksheet object? Are you able to get the range object? Are you able to read cell values?

Yes, yes, and yes. It is only when I try to change values on the worksheet that I get failures.

Note the following: I am not creating an application or workbook from within my COM Interop code. Rather, I am using the code as an Add-In and accessing an already existing application and worksheet. That is where the problem lies. In other words, if I use my COM Interop code to create a new application and worksheet, I can change whatever cells I want to change within that newly created worksheet. However, if I am using my COM Interop code as an Add-In to access an already existing application and worksheet, that is when the HRESULT 0x800A03EC exception gets thrown.

This seems to suggest that it is not possible for a COM Interop Add-In to change any data on any worksheets on existing application objects, and that COM Interop code can only change data on worksheets of application objects that it creates, itself.

Could that be the case?

 

[Mike_R]

Could that be the case?

No. The .NET code recieves a RCW ("RuntimeCallable Wrapper") from the COM Interop. It does not know how that object was created. It might have been a COM object already in memory, or a new one created via .Add() or .Open() or the like. Your .NET code does not know and does not care...

That said, your results are pretty quirky. Heres a few things to think about:

(1) The Application.CalculateFull() command does not exist in older versions of Excel. I think it might also throw an error if there is not at least one Excel workbook open at the time it is called.

(2) Im not quite sure what you are trying to do with the range.set_Item() method. I think you might be looking for the Range.set_Value() method instead?

[Edit: Ok, my bad, Range.set_Item() should be valid. (I forgot that the Range.Item() property is Read-Write -- its not usually accessed this way.) So I dont know whats wrong here. I assume that the Worksheet is not protected? You might wan to try Range.set_Value() anyway, to see if you do any better...]

Hope this helps!
Mike

 

[Mike_R]

Ok, you know what...

I believe that all of your code will fail if you do not have at least one Workbook open.

The following is an Automation example, not an Add-in, but running this from a Windows application that references Microsoft Excel should prove that your system is running right or not:

private void RunIt
{
    Excel.Application xlApp = new Excel.Application();
    xlApp.Visible = true;
    Excel.Workbook wb = xlApp.Workbooks.Add(System.Type.Missing);
    Excel.Worksheet ws = (Excel.Worksheet) wb.Worksheets[1];
    Excel.Range rng  = (Excel.Range) ws.Cells;
    int cc = 10; //ws.UsedRange.Columns.Count;
    for (int c = 1; c <= cc; c++)
    {
        rng.set_Item(10, c, "test");
    }
}

The above is substantially the same as what you had, but I added the line

Excel.Workbook wb = xlApp.Workbooks.Add(System.Type.Missing);

If the above does not run, then there is likely a problem with your Excel installation, the PIAs or something else...

-- Mike

 

[Hippo Man]

The above is substantially the same as what you had [ ... ]

If the above does not run, then there is likely a problem with your Excel installation, the PIAs or something else...

-- Mike

Thank you very much for all of this!

It turns out that the code you supplied works fine if run it as an Automation app and create my own Excel.Application object; however, when I run it in my orginal Add-In configuration (i.e., using the Excel.Application object that is passed to me in the OnConnection handler), I still get my original exception. This happens both when I use set_Item and set_Value.

Im wondering if my worksheet is indeed protected. If so, how can I un-protect it from within the C# add-in?

[P.S. - I just checked, and my worksheet is definitely not protected. What sort of incorrect installation of Excel could cause my problem to occur? I need to know what to tell the sysadmins here, in case they need to reinstall it in a different way.]

Forgive my ignorance: this is all fairly new to me, and Im learning as I go.

 

[Mike_R]

No problem, I hope we get it! Overall, I really dont know whats going wrong here.

Lets try this in your OnConnection() event handler:

private Excel.Application xlApp;
public void OnConnection(object application, Extensibility.ext_ConnectMode connectMode,
             object addInInst, ref System.Array custom)
{
    MessageBox.Show("OnConnection(): entering.");

    xlApp = (Excel.Application)application;
    MessageBox.Show("Excel Version = " + xlApp.Version);

    Excel.Workbook wb = xlApp.Workbooks.Add(System.Type.Missing);
    MessageBox.Show("Workbooks.Count = " + xlApp.Workbooks.Count.ToString());

    Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets[1];
    MessageBox.Show("Worksheet.Name = " + ws.Name);

    Excel.Range rng = (Excel.Range)ws.Cells;
    int cc = 10; //ws.UsedRange.Columns.Count;
    for (int c = 1; c <= cc; c++)
    {
        rng.set_Item(10, c, "test");
    }
    MessageBox.Show("Range.Values have been set.");

    MessageBox.Show("OnConnection(): exiting.");
}

The above really should work! If it fails, let us know where and with what error report?

What sort of incorrect installation of Excel could cause my problem to occur?

I have no idea why Automation would work but an Addin using IDTExtensibility2 would fail. If the abvoe test fails, Im thinking well have to look to make sure the PIAs are intalled right? But I really do not know what the issue is...

Ive got my fingers crossed for you!
Mike

 

[Hippo Man]

The above really should work! If it fails, let us know where and with what error report?

Well, it does indeed work. However, in that code, a brand new workbook is being created. This doesnt work for us, however, because we want our Add-In to be used within existing spreadsheets. The creation of a new workbook covers up all of the existing data in the spreadsheet.

However, if I run this same code from a function within a cell, it fails. I created the following function:

public String foo()
{
	MessageBox.Show("foo(): entering.");
 
	// xlApp is already saved in a private attribute from within the OnConnection method.
	MessageBox.Show("Application object: " + xlApp);
	MessageBox.Show("Excel Version = " + xlApp.Version);
 
	Excel.Workbook wb = xlApp.Workbooks.Add(System.Type.Missing);
 	MessageBox.Show("Workbooks.Count = " + xlApp.Workbooks.Count.ToString());
	
	Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets[1];
	MessageBox.Show("Worksheet.Name = " + ws.Name);
 
	Excel.Range rng = (Excel.Range)ws.Cells;
	int cc = 10; //ws.UsedRange.Columns.Count;
	for (int c = 1; c <= cc; c++)
	{
		rng.set_Item(10, c, "test");
	}
	MessageBox.Show("Range.Values have been set.");
 
	MessageBox.Show("foo(): exiting.");

	return ("success");
}

Then, I execute =foo() from within a cell. That COM exception gets thrown on the set_Item call.

Do I perhaps need to declare foo() differently (maybe with some sort of appropriate decorator) in order for this to work?

I have no idea why Automation would work but an Addin using IDTExtensibility2 would fail. If the abvoe test fails, Im thinking well have to look to make sure the PIAs are intalled right? But I really do not know what the issue is...

Hmm ... could this be due to the fact that were running Excel 2002 SP3 here?

Ive got my fingers crossed for you!
Mike

Thanks!

 

[herilane]

There is a fundamental difference between "normal" procedures - invoked by the user, or by events, or by other code - and worksheet functions (traditionally known as UDFs or User-Defined Functions in the Excel world).

A UDF must not change anything in the worksheet or the application. It may only return a value. To put it differently, functions called from a worksheet cell must not have any side effects.

This makes perfect sense, really. Imagine if calling a simple function like =MAX(A1:A5) could change totally unrelated cells in a worksheet. Complete chaos would ensue.

 

[Hippo Man]

There is a fundamental difference between "normal" procedures - invoked by the user, or by events, or by other code - and worksheet functions (traditionally known as UDFs or User-Defined Functions in the Excel world).

A UDF must not change anything in the worksheet or the application. ...

Well, that explains my problem. I now see that there is no way to do what I want, and therefore, I can now throw in the towel, in peace.

Thanks to all of you. This has been quite enlightening.

 

[herilane]

Perhaps we should all take a step back...

What do you really want to achieve?

 

[Hippo Man]

Perhaps we should all take a step back...

What do you really want to achieve?

Well, what I already have is a C# Add-In which has been developed using COM Interop. It provides a number of functions that return a matrix of data. I currently return these matrices via an Object[,], and as long as the user invokes them with Ctrl-Shift-Enter after selecting a range of cells, the results nicely fill these cells.

The user asked us if we could enchance the functions so that they could fill a range of cells that are specified as extra arguments to the function, and without using Ctrl-Shift-Enter. This is where I embarked on my quest to see if its possible to do what the user wants.

If this turns out to be totally impossible, its not a problem for me to get back to the user and tell him so.

Whatever we do, these functions have to be supplied via a DLL for an installable Add-In, with no macros or any other Excel objects involved. Thats because it will become part of a standard desktop build that our company creates (for several hundred users), and the intention is that the Add-In will be available to all Excel users, without them having to install any special packages or run any special spreadsheets.


Thanks.

 

[herilane]

The exact scenario you have outlined is, as you put it, totally impossible.

The closest you could get is a menu option or a toolbar button that asks the user for the parameters and does the thing.

 

[Hippo Man]

The exact scenario you have outlined is, as you put it, totally impossible.

The closest you could get is a menu option or a toolbar button that asks the user for the parameters and does the thing.

Yep, based on the earlier discussion here, thats what I surmised.

Thanks again for all your help.