Using DDE with excel

[aamoen]

Hello, I'm running the following program and running into an issue within excel.  When SAS processing the line put '[formula.fill("=SUM(U2:U299)/1000")]'; excel gives me the following message.  Not sure where I'm going wrong here..  Thanks!

Filename cmds dde 'excel|system';
Data _null_;
File cmds;
Put '[workbook.activate("MB")]';
Put '[select("R300C20")]';
put '[formula.fill("Sum of Acquisition Cost and Carrying Value")]';

put '[select("R301C20")]';
put '[formula.fill("From Sch A, col A, line M.2 & Sch A, col D, line M.2")]';

put '[select("R302C20")]';
put '[formula.fill("Check, must be zero")]';

put '[select("R300C21")]';
put '[formula.fill("=SUM(U2:U299)/1000")]';

put '[select("R300C66")]';
put '[formula.fill("=SUM(BN2:BN299)/1000")]';

put '[select("R301C21")]';
put '[formula.fill("='Schedule A'!G16")]';

put '[select("R302C21")]';
put '[formula.fill("=U300-U301")]';

put '[select("R301C66")]';
put '[formula.fill("='Schedule A'!J16")]';

put '[select("R302C66")]';
put '[formula.fill("=BN300-BN301")]';

run;

[Reeza · Accepted solution]

I think formula.fill is correct but don't you need to use the RC notation, not the cell reference notation for DDE?

See page 14 here
https://support.sas.com/resources/papers/proceedings/proceedings/sugi31/022-31.pdf

[Reeza]

And formula.fill appears to have two parameters - one is the formula, the second appears to be the range it should fill, which makes sense given the FILL.

[aamoen]

Okay. I thought the put, select directly above determined the cell within excel to fill the formula.

put '[select("R300C21")]';
put '[formula.fill("=SUM(U2:U299)/1000")]';

[Reeza]

You'll have to check the documentation then, I'm making an educated guess based on the code I've seen.
Are you sure you can't use ODS EXCEL instead of DDE?

[aamoen]

yep, that was it. Need to use the RC notation, not the cell reference.

Thank you!