topic Re: Using DDE with excel in SAS Programming

Using DDE with excel

aamoen — Fri, 16 Jul 2021 14:38:03 GMT

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;

Re: Using DDE with excel

Reeza — Fri, 16 Jul 2021 15:14:25 GMT

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

Re: Using DDE with excel

Reeza — Fri, 16 Jul 2021 15:15:54 GMT

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.

Re: Using DDE with excel

aamoen — Fri, 16 Jul 2021 15:20:13 GMT

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")]';

Re: Using DDE with excel

Reeza — Fri, 16 Jul 2021 15:30:25 GMT

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?

Re: Using DDE with excel

aamoen — Fri, 16 Jul 2021 15:33:06 GMT

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

Thank you!