Excel Save As Macro

JamesNewcombe — Wed, 10 Oct 2012 09:04:09 GMT

macroHello

I have written a SAS macro to open an excel spreadsheet using dde. I've also got one to do a "save as"

%macro excelsaveas(ifile, ilength);

filename cmds dde 'excel|system';

data _null_;

file cmds;

put '[error(false)]'; *prevent excel asking "are you sure?";

put &ifile;

x=sleep(&ilength.);

put "[QUIT()]";

x=sleep(&ilength.);

run;

%mend;

I can call the macro like this

%excelsaveas(%str('[SAVE.AS("C:\TEMP\test2.xls")]'), 5);

The first parameter is the save-as name and location. The second parameter is how many seconds SAS should sleep for before continuing, because SAS doesn't know when the execution of the save-as command has been completed.

This works fine, however what I want to do is include a macro variable in the save-as name of the spreadsheet. This will allow me to save the spreadsheet with the date in the name, which would be really useful! However, I can't find any combination of double/single quotes or quoting functions that work. I've also tried rewriting the macro in various ways, but SAS always comes back with an error.

Has anyone done something like this before, is it possible, and if so how?

Many thanks

Re: Excel Save As Macro

ballardw — Wed, 10 Oct 2012 14:52:07 GMT

I think you would be much better off trying to build the save as part in the macro instead of as a parameter. The various quoting and unquoting functions in SAS will be a nightmare with that whole statement.

I solved a similar bit with following: (xlsx not xls but logic is the same)

put %unquote(%str(%'[SAVE.AS("&name..xlsx",51)]%'));

Your macro call using just the path and file name

%excelsave(%str(c:\temp\test2.xls),5);

with line instead fo put &ifile

put %unquote(%str(%'[SAVE.AS("&ifile",51)]%'));

topic Excel Save As Macro in SAS Programming

Excel Save As Macro

Re: Excel Save As Macro