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
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.
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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.