The SAS Output Delivery System and reporting techniques

Refresh Excel Pivot from SAS ??

Accepted Solution Solved
Reply
Regular Contributor
Posts: 173
Accepted Solution

Refresh Excel Pivot from SAS ??

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;


Accepted Solutions
Solution
‎02-11-2015 09:44 AM
Super User
Posts: 17,750

Re: Refresh Excel Pivot from SAS ??

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?

View solution in original post


All Replies
Super User
Posts: 17,750

Re: Refresh Excel Pivot from SAS ??

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?

Regular Contributor
Posts: 173

Re: Refresh Excel Pivot from SAS ??

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!

Super User
Super User
Posts: 7,392

Re: Refresh Excel Pivot from SAS ??

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.

Solution
‎02-11-2015 09:44 AM
Super User
Posts: 17,750

Re: Refresh Excel Pivot from SAS ??

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?

Regular Contributor
Posts: 173

Re: Refresh Excel Pivot from SAS ??

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;

Contributor
Posts: 29

Re: Refresh Excel Pivot from SAS ??

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 1404 views
  • 0 likes
  • 4 in conversation