topic Re: How to troubleshoot Physical file does not exist? in SAS Programming

How to troubleshoot Physical file does not exist?

afiqcjohari — Fri, 31 Mar 2017 08:14:27 GMT

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.

Re: How to troubleshoot Physical file does not exist?

Yavuz — Fri, 31 Mar 2017 05:24:37 GMT

Could you send log please. I dont sure but
outfile="&xlsfile1" or outfile=&xlsfile1

Re: How to troubleshoot Physical file does not exist?

Reeza — Fri, 31 Mar 2017 05:42:42 GMT

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?

Re: How to troubleshoot Physical file does not exist?

ballardw — Fri, 31 Mar 2017 15:31:49 GMT

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.