Writing directly to an Excel range from COM add-in

Add-in Express™ Support Service
That's what is more important than anything else

Writing directly to an Excel range from COM add-in
Is it possible to write a value directly to a cell from the ribbon? 
David Jones


Guest


Hi,

Can you advise me of the best way to write to an Excel workbook from a COM add-in (i.e. ribbon)?

My problem is that I have data arriving asynchronously on a worker thread and I must update cells in an open workbook with this data. I have tried various methods to achieve this, but none seem to work satisfactorily. The best solution I have is to call ADXAddInModule.SendMessage() from the worker thread and then write the data to the sheet using Excel Interop in the message handler. However, this approach has a number of problems:

- It fails if the user is editing the sheet (although I can detect/handle this problem with 99% reliability)
- It causes problems with dialogs. Modeless dialogs lose focus and edit boxes and other controls (even on modal dialog boxes) lose focus even if the user is editing them.
- It causes other undefined/undesirable behaviour in Excel. For example, if the user is moving or resizing the Excel window, it will jump back to its original size/position.
- Performance is awful, and Excel displays a wait cursor while it is writing the data.

Is there a better way to do this? Is it possible to write to the sheet using ADXExcelRef.SetValue() and would this offer any improvement? When I try this it fails and throws an exception.

Please note that in other areas of the product I use a UDF-wrapped RTD server to achieve this, but this approach is not possible in this instance (and it has its own problems related to array formulas, documented here and elsewhere).

Any help on this matter is greatly appreciated.

Kind regards,
David
Posted 23 Apr, 2015 05:18:23 Top
Andrei Smolin


Add-in Express team


Posts: 18787
Joined: 2006-05-11
David,

David Jones writes:
Is there a better way to do this?


Try this approach. In the COM add-in you call ExcelApp.ExecuteExcel4Macro("MyWriteMethod()") to write to the cells (in the OnSendMessage event handler). In the XLL (XLLContainer), you define this method:

public static void MyWriteMethod(){
    // if (%check if it is possible to insert data e.g. call some property of the AddinModule%) return;
    ADXExcelRef range = AddinModule.CurrentInstance.GetRange();
    object value = AddinModule.CurrentInstance.GetData();
    bool result = range.SetValue(value);
}


These are definitions of the sample methods in the add-in module:

public object GetData(){
    string[,] arr = InitializeData();
    return arr;
}

public AddinExpress.MSO.ADXExcelRef GetRange(){
    return new ADXExcelRef(0, MaxRows - 1, 0, MaxColumns - 1, "Sheet1", false);
}


I suppose Excel will call this MyWriteMethod() in the special context and this will solve the problems above. I'm not sure that this approach it won't lead to new problems.

My I ask you to share your results with the forum readers?


Andrei Smolin
Add-in Express Team Leader
Posted 23 Apr, 2015 09:08:37 Top
David Jones


Guest


Thanks for your suggestion. I have tested the idea extensively today and, while it does work quite well, you're right in that it just introduces different issues. The biggest problem I found is that highlighted ranges jump to the cells being written, particularly when trying to enter a formula in another cell, rendering Excel unusable.

However, in the course of testing your proposal I discovered what is causing the problem with the original solution: it is the function you describe https://www.add-in-express.com/creating-addins-blog/2011/03/23/excel-check-user-edit-cell/ to determine whether Excel is in edit mode. Specifically, putting Excel in/out of interactive mode in the function IsEditing() causes the problem with dialogs, controls, resizing, etc. I use this function very successfully in other parts of the application, but always in response to a user action (e.g. button click). It seems it has undesirable side-effects when used to handle asynchronous data, even if that data is posted to the UI thread.

So I'm back to square-one: I have solved the immediate problem, but now I must look for an alternative way to tell if Excel is editing, or at least an alternative way to handle this (common) scenario.
Posted 23 Apr, 2015 13:59:39 Top
Andrei Smolin


Add-in Express team


Posts: 18787
Joined: 2006-05-11
David,

In that blog, check section A solution for Excel 2007 ?Â?Ð?ã 2010.


Andrei Smolin
Add-in Express Team Leader
Posted 24 Apr, 2015 01:58:05 Top