Question - DDE to save an Excel file thru SAS w/o it making a backup file?

SASHunter — Fri, 28 May 2010 13:45:50 GMT

I am using SAS 9.2 and Excel 2007.

I have a program that works well. It uses PROC EXPORT to create an Excel file. Then calls Excel to open and I have a workbook that has some macros that format the sheet. This all works well. At this point I have SAS running. Excel is open with the workbook that I have Exported and the workbook that contains the macros. The macros run and I can see that the workbook that was exported is being updated correctly. What happens then is I save this exported Excel workbook that has been updated to the original name that I used in the PROC EXPORT. I am using DDE commands.

/***************************************************/
/* Export the new file to Excel 2007 */
/***************************************************/
PROC EXPORT data = lhost.chdaily2
(keep = Category Cumulative d:)
outfile = "&mydir.\chdaily.xlsx"
DBMS = EXCEL REPLACE;
SHEET="Sheet1";
run;

.....
The above works fine.

data _null_;
file cmdexcel;
put &macrfile; *open the file containing the macro;
put &chekfile; *open the daily checkin file;
put '[RUN("chdaily_test_.xlsm!Macro_Daily_Checkin")]';
*run the macro;
run;

....
This works fine.

/* Save the changes made */
%let excelout = &mydir.\chdaily.xlsx;
data _null_;
file cmdexcel;
put '[error(false)]';
put "[save.as(%bquote("&excelout",51))]";
run;

At this point it for some reason it makes a backup file in the directory that it is sitting.

Can anyone tell me why? And if I can stop it from doing the backup.
Also, the line above that says - put '[error(false)]' - how would I write this for the excel vba - Application.DisplayAlerts = false - ?

Thanks, Nancy

Re: Question - DDE to save an Excel file thru SAS w/o it making a backup file?

SASHunter — Tue, 08 Jun 2010 19:47:12 GMT

OK, I figured it out.

In this part:

/* Save the changes made */
%let excelout = &mydir.\chdaily.xlsx;
data _null_;
file cmdexcel;
put '[error(false)]';
put "[save.as(%bquote("&excelout",51))]";
run;

---------------------------------------------------------------------
the above put '[save.as(......)]'; is now like this:

put "[save.as(%bquote("&excelout",51,,false))]";

The FALSE makes it so no backup is created.
I know everyone probably is saying, that they knew that already. So for all those people that didn't know. I hope this helps.

Happy programming -- Nancy

topic Re: Question - DDE to save an Excel file thru SAS w/o it making a backup file? in SAS Procedures

Question - DDE to save an Excel file thru SAS w/o it making a backup file?

Re: Question - DDE to save an Excel file thru SAS w/o it making a backup file?