The SAS Output Delivery System and reporting techniques

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

Reply
Contributor
Posts: 54

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

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

Grand Advisor
Posts: 17,474

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

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. 

 

 

 

Trusted Advisor
Posts: 1,114

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

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.

 

 

Ask a Question
Discussion stats
  • 2 replies
  • 414 views
  • 0 likes
  • 3 in conversation