I have successfully fit columns, formatted ranges, etc using DDE, but it is certainly not for the faint of heart or anyone under significant time constraints.
Here is some example code:
/* assumes that Excel is already running */
filename cmds dde 'excel|system';
data _null_;
file cmds;
put "[OPEN(""C:\Temp\Experiment.xls"")]";
put "[ERROR(""FALSE"")]";
put "[workbook.delete(""Place_holder"")]";
put "[ERROR(""TRUE"")]";
put "[workbook.select(""class"")]"; /* select sheet */
put "[select(""r2c3:r20c3"")]"; /* select all of column 3 */
put "[format.number(""yyyy/mm/dd"")]"; /* format as yyyymmdd */
put "[select(""r2c4:r20c5"")]"; /* select columns 4 and 5 */
put "[format.number(""#,##0.00"")]"; /* format with 2 decimals and comma*/
put "[select(""r1c1:r1c5"")]"; /* select headers */
put "[font.properties(,""Bold"")]"; /* make them bold */
put "[alignment(3,""TRUE"",3,0)]"; /* center,wrap,bottom,no orientation */
put "[SELECT(""R1C2"")]"; /* select one header */
put "[alignment(,""FALSE"",,1)]"; /* no wrap, vertical orientation */
put '[COLUMN.WIDTH(1,"C1:C5",,3,1)]'; /* autofit columns 1 thru 5 */
put '[ERROR("TRUE")]';
put '[ERROR("FALSE")]';
put '[SAVE()]';
put '[ERROR("TRUE")]';
put '[CLOSE("FALSE")]';
run;
filename cmds clear;
... View more
