Update many cells

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

Update many cells
 
ewessely




Posts: 35
Joined: 2019-01-31
Hi,
my XLL-UDF triggers a task (running async) for downloading data from a service.
Following your suggestions in this forum the task stores the data somewhere and use "SendMessage" to signal that it's finished.
In the AddInModule I added the OnSendMessage and react to my message.

In the next step I've to write the data.
As you warn in multiple posts to use the Excel Object Model in XLL I would like to fill in and format the cells in the AddInModule.
The missing link here is to access the Excel.Range. I've seen many examples using the ExcelApp.ActiveSheet, but in the resulting data I have the SheetId, row and column (of the upper left corner) where to place the data. So my main problem for the moment is to get the sheet from the sheetId in the COM AddInModule.

Currently I'm doing the output by calling a function definde in the XLL via ExcelApp.ExecuteExcel4Macro("WriteData()"). But as you suggest I would like to avoid using the Excel Object Model.

I'm also open for other performant and safe ideas to manipulate many cells async.

Many thanks for help!
Erich
ew
Posted 25 May, 2020 12:49:25 Top
Andrei Smolin


Add-in Express team


Posts: 17290
Joined: 2006-05-11
Hello EW,

Below is a raw sketch describing the idea.

XLL:
ADXExcelRef someExcelRef = ... ; 
// e.g. someExcelRef = (ADXExcelRef)Module.CallWorksheetFunction(ADXExcelWorksheetFunction.Caller);
string address = someExcelRef.ConvertToA1Style();


Here you put the address in a global Dictionary<int, string> and pass the key to the add-in module:
int myMessage = {an integer> 1024 here};
theAddinModule.SendMessage(myMessage, (IntPtr)theKey, IntPtr.Zero)



When the COM add-in receives myMessage, it gets the key: it theKey = (int)e.WParam. Then it retrieves the address string and get the range:
Excel.Range range = ExcelApp.Range[address, Type.Missing];


Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 26 May, 2020 06:14:50 Top
ewessely




Posts: 35
Joined: 2019-01-31
Hello Andrei,
thanks for the very prompt reply! I'll give it a try.

Best regards
Erich

PS: pls delete my duplicate post - don't know why it's there
ew
Posted 26 May, 2020 08:17:25 Top
Andrei Smolin


Add-in Express team


Posts: 17290
Joined: 2006-05-11
You are welcome, Erich!

ewessely writes:
PS: pls delete my duplicate post


Done.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 26 May, 2020 08:34:44 Top