BookmarkSubscribeRSS Feed
afiqcjohari
Quartz | Level 8
data cars;set sashelp.cars;run;
%let xlsfile1 = C:\Users\1558977\Desktop\autocreate\cars.xlsx;

proc export outfile="&xlsfile1" data=cars dbms=xlsx replace;
run; 
quit;

   * This next routine starts the Excel application.   *;
filename cmds dde 'excel|system';
x "'C:\Program Files\Microsoft Office\Office11\excel.exe'";
data _null_;
   x=sleep(3);
run;

data _null_;
   file cmds;
   put '[open("'"&xlsfile1"'")]';
run;

The intent is to open the excel file and save it password protected.

But SAS keeps giving this error:Physical file does not exis, excel|sytem

 

UPDATE: The error is linked to excel.exe file not found. If I have Excel running already, the code works fine. So need to search for that excel.exe. 

 

data cars;set sashelp.class;run;
%let xlsfile1 = C:\Users\1558977\Desktop\autocreate\cars.xls;
%let xlsfile2 = C:\Users\1558977\Desktop\autocreate\carspassword.xls;

proc export outfile="&xlsfile1" data=cars dbms=xls replace;
run; 
quit;

* This next routine starts the Excel application.   *;
filename cmds dde 'excel|system';

data _null_ ;
	file cmds;
	put '[open("'"&xlsfile1"'")]';
	put '[SAVE()]';
	put '[Save.as("'"&xlsfile2"'",1,"'"test"'")]';
	PUT '[FILE-CLOSE()]';
run;

I've amended by deleting the point towards excel.exe. But for this to work, it requires Excel to be opened first.

 

3 REPLIES 3
Yavuz
Quartz | Level 8
Could you send log please. I dont sure but
outfile="&xlsfile1" or outfile=&xlsfile1
Reeza
Super User

Does Excel open? What step is it failing at? 

 

What happens if rather than a macro variable for the open command, you use the full path?

ballardw
Super User

Post the actual log with the code. Your  example has a couple of different opportunities for a file not to exist.

 

Also are you running in a server environment? If so you may have mulitple other issues such as the paths not being recognized bythe server (causing file not to exist) and that Excel is not available.

 

The DDE Excel SAVEAS command wants a file format indicator (you may be using 1 for that in your syntax) that is variable depending ont the file type created such as XLSX or XLS (different formats) and depends on the version of EXCEL doing the saving. For example 56 is to save as an XLS file from Excel 2007-2010. At least in the last working DDE example I have.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 3 replies
  • 2291 views
  • 0 likes
  • 4 in conversation