BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
KevinC_
Fluorite | Level 6

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

6 REPLIES 6
Reeza
Super User

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_
Fluorite | Level 6

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Super User

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_
Fluorite | Level 6

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
Quartz | Level 8

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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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