Refreshing Pivot Table from SAS

[antenga]

Hi,

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

https://communities.sas.com/t5/SAS-Procedures/Updating-Extrenal-Excel-Pivot-Table-in-SAS/td-p/167515...

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.

[Reeza]

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]

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

I was debugging this and had fixed this.