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

HunterN

Active Member
Joined
Mar 19, 2002
Messages
479
I asked this in the SAS Discussion Forums, but no replies. I am hoping that someone here can help me solve this problem.

I am using SAS 9.2 and Excel 2007.

I have a SAS 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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Forum statistics

Threads
1,214,644
Messages
6,120,709
Members
448,983
Latest member
Joaquim_Baptista

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top