Edit the Result object

Feb 14, 2012 at 10:31 AM

Hi,

I'm displaying table data into excel using excel function.

DataTable dtResult = GetValuesFromDB();

object[,] objResult = GetCommaSepratedValuesFromDataTable(dtResult)

return XlCall.Excel (XlCall.xlUDF,"Resize",objResult)

It's displaying data into excel cells with formulas applied for all the cells.

I want to edit the value of one of the column and then resubmit/update the data. But When I'm trying to edit the Cell, it's showing me a message as "You cannot change  part of an array."

If I copy all the cell and do Paste Special with Values & then try to change the value, it works fine. But i don't want to do that exercise.

Thanks,

Leo

Coordinator
Feb 14, 2012 at 11:37 AM

Hi Leo,

The "Resize" helper function creates an array formula of the right size for your result. Excel prevents you from changing parts of that array formula. So basically the behaviour that you report is correct.

You could change the "Resize" helper function to paste the data instead, of change your function to be a macro triggered from a menu that just pastes the data into the sheet. Then you'd be able to edit it, but it wouldn't be 'live'.

-Govert

Feb 14, 2012 at 12:58 PM

Hi Govert.

Appreciate your reply on this.

Well, I always have to call the Functions from Excel which is macro triggred as user will always pass some parameter values while calling the function.

Is there any way by which i can get Range info that result data take? I mean, after calling the function from B1 cell, it'll call my webservice fun. &  display the data from B1:K10. Can I get this range value i.e. - B1:K10? and then will try to clear formulas from those cell range. Is it possible? Or can you send me some hint to change Resizer function?

Regards, Leo

Coordinator
Feb 14, 2012 at 2:24 PM

Hi Leo,

I don't currently have a more general purpose example of aysnchronously running a macro after a function calculated.

The only suggestion I have in terms of understandinf the Resizer function is that you need to get hold of the Excel XLMACR8.HLP help file which documents the old macro functions. These are use through the C API, in the Resizer example.

Currently the resizer sets the array formula of the range, you just want to set the values instead of the formula, is that right?

-Govert

Feb 21, 2012 at 4:12 AM
Edited Feb 21, 2012 at 5:25 AM

Hi Govert,

Yes, I would like to set values instead if range formula in resizer.

I've also read your post below, where you mentioned to convert from A1-style to R1C1-style http://groups.google.com/group/exceldna/browse_thread/thread/294126ae1efd0eef

But if i look at the Resizer class,

formulaR1C1 = (string)XlCall.Excel(XlCall.xlfFormulaConvert, formula, true, false, ExcelMissing.Value, firstCell);

I tried to change the Resizer class code but no luck.!

can you please help me for that?

Regards, Leo