topic Re: Refresh Excel Pivot from SAS ?? in ODS and Base Reporting

Refresh Excel Pivot from SAS ??

KevinC_ — Tue, 10 Feb 2015 23:31:06 GMT

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;

Re: Refresh Excel Pivot from SAS ??

Reeza — Tue, 10 Feb 2015 23:39:41 GMT

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?

Re: Refresh Excel Pivot from SAS ??

KevinC_ — Wed, 11 Feb 2015 14:36:24 GMT

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!

Re: Refresh Excel Pivot from SAS ??

RW9 — Wed, 11 Feb 2015 14:43:43 GMT

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.

Re: Refresh Excel Pivot from SAS ??

Reeza — Wed, 11 Feb 2015 14:44:05 GMT

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?

Re: Refresh Excel Pivot from SAS ??

KevinC_ — Wed, 11 Feb 2015 15:07:26 GMT

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;

Re: Refresh Excel Pivot from SAS ??

GenDemo — Wed, 05 Apr 2017 00:40:51 GMT

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