[SOLVED] SAS protect Excel worksheet with DDE

SAS protect Excel worksheet with DDE

I want to manipulate an existing Excel sheet with DDE using SAS:

I have the following code (be careful! I use z for r(ows) and s for c(columns) because of German language settings in Excel):

option noxwait noxsync;
x call "C:\Program Files (x86)\Microsoft Office\Office15\EXCEL.EXE";

%let delay=5;

data _null_;
rc=sleep(&delay); 
run;

filename sas2xl dde 'excel|system';

data _null_;
rc=sleep(&delay);
run;

%let mapwkbk=H:\odstest.xlsx;

data _null_;
file sas2xl;
rc=sleep(&delay);
put '[open("' "&mapwkbk" '")]';
run;


filename random dde 'excel|Daten!z2s1:z100s3';
data _null_;
    set sashelp.class;
    file random;
    put name sex age;
run;


data _null_;
file sas2xl;
/*rc=sleep(&delay);*/
put '[workbook.select("Pivot")]';
put '[select("Z2S1")]';
put "[pivot.refresh()]"; 

put '[workbook.select("Daten")]';
put '[select("z1S2:z1s5")]';
put '[filter]';
put '[select("z1S10")]';
put '[filter]';

put '[column.width(0,"s1",false,1)]';
put '[column.width(0,"s6:s9",false,1)]';

put '[ActiveSheet.Protect("***")]';

put '[workbook.delete("Dim")]';


/*put "[save()]";*/
/*put "[quit()]";*/
run;

Now I would like to password protect the sheet Daten and set option use autofilter active. Any ideas how to reach this?

Solution

My recommendation here is to write a macro to do what you're trying to do (both questions here really), and then call that macro using DDE if you must use DDE. That's far easier to do than to use DDE to do every little thing, and far easier to maintain (plus more portable when you eventually move away from DDE).

If you do that, then your macro code doesn't have all the extra SAS language bits around it, and can be more easily read/maintained (even by a VBA developer rather than a SAS developer), and SAS just calls the macro which is easy to do.

DDE is an old technology not well supported anymore, and new development should move away from it where possible.