topic Re: SAS and Excel DDE: unhide columns and rows of each worksheet in a excel file in ODS and Base Reporting

SAS and Excel DDE: unhide columns and rows of each worksheet in a excel file

wtien196838 — Tue, 26 Apr 2016 17:58:27 GMT

Dear SAS support community users:

I use a lot of sas programs to verify the excel format reports.

The procedure is coded in sas:

step 1: open particular excel program :

options noxwait noxsync ;

%let Excel_path = 'C:\Program Files (x86)\Microsoft Office\Office15\EXCEL.EXE' ;
Data _null_ ;
x = sleep(8);
run;

data _null_;
file DDEcmds;
put '[OPEN(<path.>\test.xls)]';
z = sleep(8);

run;

step 2: MANUAL UNHIDE all columns and rows in each worksheet

step 3: run sas program

Is it possible that merging step 1 and step 2 together ?
My purpose is unhide all columns and rows in each worksheet by coding.

Thanks for your information.

Regards,

William

Re: SAS and Excel DDE: unhide columns and rows of each worksheet in a excel file

Reeza — Tue, 26 Apr 2016 20:43:13 GMT

Yes and no.

You could move up, but it's better to be safe and wait for Excel to open the file first - that's what the 8 seconds is for. That seems a bit high though, so you may be able to reduce that to 2/3 if your system is fast.

Your first data _null_ step doesn't do anything by the way...but I'm assuming you've only posted portions of code because the following doesn't make a lot of sense.

Re: SAS and Excel DDE: unhide columns and rows of each worksheet in a excel file

FreelanceReinh — Tue, 26 Apr 2016 22:28:26 GMT

Hello William,

Section 6, item 6 (p. 5) of this famous SUGI paper on DDE by Koen Vyverman explains how widths of a range of columns can be changed to a best-fit width. (It's also possible to specify a width numerically.) The corresponding syntax for row heights seems to be exactly analogous, according to this SUGI paper, p. 12, Fig. 11. I think, these techniques (with suitably chosen cell ranges) can also unhide columns/rows, as they are hidden because of width/height equal to zero. Since I don't have Excel on my workstation, I cannot test it, unfortunately.