03-31-2017 12:30 AM - edited 03-31-2017 04:14 AM
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 errorhysical 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.
03-31-2017 01:42 AM
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?
03-31-2017 11:31 AM
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.