Refreshing Pivot Table from SAS

antenga — Mon, 02 Oct 2017 23:27:47 GMT

Hi,

I have been trying to automate refreshing a pivot table from SAS and followed the previous suggestions from the link below :-

However I still get the DDE Error :-

ERROR: DDE session not ready.
FATAL: Unrecoverable I/O error detected in the execution of the DATA step program.
Aborted during the EXECUTION phase.

Code:-

options noxwait noxsync;

data _null_;
rc=system('start excel');
rc=sleep(5);
run;

filename cmds dde 'excel|system';

data _null_;

file cmds;

put "[open(""&path\&f_name..xls"")]";

put '[workbook.select("Pivot")]';

put '[select("R1C4")]';

put "[pivot.refresh()]";

put "[save()]";

put "[quit()]";

run;

quit

Here the Excel file is a 2003 file and I have debugged this code. I get the error with this code line ' put "[pivot.refresh()]"; '

Please advice.

Re: Refreshing Pivot Table from SAS

Reeza — Tue, 03 Oct 2017 00:11:34 GMT

Is that the correct command for an XLS file? What's the command in the Microsoft 4.0 guide?

Also, is your pivot table starting in that cell?

antenga — Tue, 03 Oct 2017 00:27:30 GMT

Apologies, as that was an error on my side as it should be R4C1.

I was debugging this and had fixed this.