| I have seen the example given by the SAS Institute in the help files for writing data to an Excel v5 spreadsheet, but can you write data from a SAS dataset into a 123 v9 spreadsheet using OLE?
|
The following SAS version 8 code has similar functionality to the Excel example provided by the SAS Institute, but will also create a 123 spreadsheet called SASDATA.123 in your default directory. Note that the buttons on the Frame asscociated with this SCL should be called B_LAUNCH, B_CREATE, B_CLOSE and B_EXIT:
length dsn $17 sheetfile $80 alpha $27 textcol $8;
INIT:
/* Initialise values */
dsn='sashelp.class';
sheetfile='sasdata.123';
alpha=' abcdefghijklmnopqrstuvwxyz';
return;
MAIN:
return;
TERM:
/* Close SAS table, if open */
if dsid > 0 then dsid=close(dsid);
return;
B_LAUNCH:
/* Open 1-2-3, and make visible */
hostcl = loadclass('sashelp.fsp.hauto');
call send (hostcl, '_new',sessobj, 0, 'Lotus123.Workbook.98');
call send (sessobj, '_getProperty', 'Application', appobj);
call send (appobj, '_setProperty', 'Visible', 'True');
return;
B_CREATE:
/* Create new document */
call send (appobj, '_do', 'NewDocument');
call send (appobj, '_getProperty', 'ActiveDocument', adobj);
/* Open SAS table, and count columns and rows */
dsid=open(dsn, 'i');
call set(dsid);
rc=fetch(dsid);
nvar=attrn(dsid, 'NVARS');
nobs=attrn(dsid, 'NOBS');
/* Write column names to 1st row of sheet */
do col=0 to (nvar-1);
sheetcol=col+1;
sheetrow=1;
link col2text;
call send (adobj, '_getProperty', 'Ranges', trim(textcol), retcell);
var=varname(dsid, sheetcol);
call send (retcell, '_setProperty', 'Contents', var);
end;
/* Write SAS table row values to sheet rows */
do while (rc ne -1);
do row = 0 to (nobs-1);
sheetrow=row+2;
do col = 0 to (nvar-1);
sheetcol=col+1;
link col2text;
call send (adobj, '_getProperty', 'Ranges', trim(textcol), retcell);
if vartype(dsid, sheetcol) eq 'N' then var=put(getvarn(dsid, sheetcol), 8.);
else var=getvarc(dsid, sheetcol);
call send (retcell, '_setProperty', 'Contents', var);
end;
rc=fetch(dsid);
end;
end;
return;
B_CLOSE:
/* Save and close sheet */
call send (adobj, '_do', 'SaveAs', sheetfile, '', '1-2-3 (123)', 'False', '', 'True');
call send (appobj, '_getProperty', 'ApplicationWindow', awobj);
call send (awobj, '_do', 'Close', 'False');
return;
B_EXIT:
/* Close 1-2-3 and application */
call send (appobj, '_do', 'Quit');
call send (appobj, '_term');
call execcmd('cancel;');
return;
COL2TEXT:
/* Convert SAS table column and row to 1-2-3 cell notation: e.g. "4,25" to "d25" */
textcol=left(substr(alpha, int(sheetcol/27)+1, 1)!!
substr(alpha, mod(sheetcol, 27)+1, 1)!!
trim(left(put(sheetrow, 8.)))
);
return;
|
