Refresh Excel Pivot from SAS ??

[KevinC_]

Hello Everyone,

I am trying to refresh an Excel Pivot from SAS.  I get this error : Physical file does not exist, excel | system.  The code opens Excel but it just sits there.  Any input would be greatly appreciated!

Here is my SAS code:

data _null_;

"C:\My_Doc");

"Customer1";

Call SYMPUTX('path', path, 'G');

Call SYMPUTX('f_name', f_name, 'G');

run;

options noxwait noxsync;

data _null_;

   rc=system('start excel');

run;

filename cmds dde 'excel|system';

data _null_;

file cmds;

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

put '[workbook.activate("Pivot1")]';

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

put '[pivot.refresh()]';

put "[save()]";

put "[quit()]";

run;

quit;

[Reeza]

Did your first data step get cut off in the copy/paste?

EDIT:

You didn't include the sleep command, go to give Excel a chance to start up. What happens when you include it?

[KevinC_]

Hi Reeza,

oops, yes, the first dataset got cut off.

Here it is:

data _null_;

path = trim("C:\My_Doc");

f_name = "Customer1";

Call SYMPUTX('path', path, 'G');

Call SYMPUTX('f_name', f_name, 'G');

run;

Btw, the sheet that contains the pivot is named "Pivot1".  I hope I referenced it properly.

Thanks!

[RW9]

Hi,

Well, as I always suggest, don't use DDE.  It is very old technology and no indication when it will be supported to.  Also, a lot of the newer version technicalities are not implemented.  Address what it is your trying to do.  If you have a working Excel file which needs updated data then do that, i.e. export data from SAS as CSV, then in your Excel file write a VBA macro to import this and put it into the various Excel things.  Plenty of examples on the web about how to load CSV files and process them in Excel.  So, rather than trying to force another application to do your bidding from SAS, use that application to pull data in, you wouldn't for instance, if you were working in a database start sending commands out to SAS, you would export your data and then work in SAS, or use ODBC or something similar to pull in the data.

[Reeza · Accepted solution]

When coding DDE I like to test each line as it runs to make sure it's doing what I want it to.

You ou can do this by commenting out rest of the lines and seeing where the process stops.

DId you try adding the sleep() command in?

[KevinC_]

Thank you guys for your input.

Reeza, I added a sleep command and ran the code one line at a time and I got it to work!  One major difference I made was that I added a VBA macro (Refresh) to the Excel,  and I added a 'RUN' command.   I am glad this works.  However, if anyone has suggestions on how to tackle this in a different way I would love to hear it.  Thank you Reeza, RW9 again!

Here is the code that worked :

options noxwait noxsync;

data _null_;

   rc=system('start excel');

run;

filename cmds dde 'excel|system';

data _null_;

file cmds;

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

PUT '[RUN("Refresh")]';

put "[save()]";

put "[quit()]";

run;

[GenDemo]

Hi All

This is great information BTW!

I am now also playing around with this, to get dashboards automated.

Is there a way to get SAS to push the command to refresh all data (like by clicking the refresh all button on the data tab in Excel), which will refresh all tables, pivots and external data from DBMS connections, without having a maco in the workbook? So I have a xlsx file, not an xlsm file - no macros in the file itself.

Thnx

GD