Date:         Thu, 28 Sep 2000 11:42:34 +0100
Reply-To:     Peter Crawford <peter.crawford@DB.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Peter Crawford <peter.crawford@DB.COM>
Subject:      excel4/5  ... was : writing to a different worksheet ..
Comments: To: autretx@my-deja.com
Content-type: text/plain; charset=iso-8859-1

now there's a good idea !
   "try to get Excel 5.0 and make a
   macro with language Excel 4.0"

In a legal sort of way, how would one obtain a legitimate copy of that old software ?
Excel 5.0 or  Excel 4.0

it might be a whole lot simpler than all these hoops we seem to struggle jumping thru'



Datum:         28/09/2000 10:36
An:            SAS-L@listserv.uga.edu




Antwort an:    autretx@my-deja.com

Betreff:       Re: writing to a different worksheet in an excel file than the              first one
Nachrichtentext:


You can do that in DDE:

French example:

filename CMD dde'excel|system';

data _NULL_;
   file CMD;
   put '[SEL.CLASSEUR("Feuil5")]';
   put '[INSERER.CLASSEUR(1)]';
run;

For guessing the english equivalent, try to get Excel 5.0 and make a
macro with language Excel 4.0 the code is DDE code.

You can have a look to VBA commands

SAS examples:
The following example uses SCL code to populate a Microsoft Excel
spreadsheet with data from a SAS data set:
Load an instance of the OLE Automation class and invoke  Microsoft
Excel as the new object.  Set the object's Visible attribute  to true
so you can see the automation.

 LAUNCHXL:
 hostcl = loadclass('sashelp.fsp.hauto');
 call send (hostcl,  '_NEW_',     excelobj, 0,
                  'Excel.Application.5');
 call send (excelobj,'_SET_PROPERTY_','Visible','True');
 return;


Get the identifier for the current Workbooks property and  add a
worksheet to it.  Then get the identifier for the new worksheet.

 CREATEWS:
 call send (excelobj,'_GET_PROPERTY_','Workbooks',
                   wbsobj);
 call send (wbsobj,  '_DO_',      'Add' );
 call send (excelobj,'_GET_PROPERTY_','ActiveSheet',
                   wsobj );


Open a SAS data set.

 dsid=open('sasuser.class','i');
 call set(dsid);
 rc=fetch(dsid);
 nvar=attrn(dsid, 'NVARS');
 nobs=attrn(dsid, 'NOBS');


Traverse the data set and populate the cells of the  Excel worksheet
with its data, row by row.

 do col=1 to nvar;
  call send (wsobj, '_COMPUTE_', 'Cells',1,col,retcell);
  var=varname(dsid,col);
  call send (retcell, '_SET_PROPERTY_', 'Value' ,var);
 end;
 do while (rc ne -1);
   do row = 1 to nobs;
     do col = 1 to nvar;
        r=row+1;
       call send (wsobj,   '_COMPUTE_', 'Cells', r ,col,
                        retcell);
   if vartype(dsid,col) eq 'N' then var=getvarn(dsid,col);
    else var=getvarc(dsid,col);
       call send (retcell,'_SET_PROPERTY_', 'Value' ,var);
     end;
     rc=fetch(dsid);
   end;
  end;
 return;


Close the worksheet and end the Excel session.

 QUITXL:
 call send (excelobj,'_GET_PROPERTY_', 'ActiveWorkbook',
                   awbobj );
 call send (awbobj,  '_DO_', 'Close', 'False' );
 call send (excelobj,'_DO_', 'Quit' );
 call send (excelobj,'_TERM_' );
 return;


As you can see from this example, automating an application object
requires some knowledge of the object's properties and methods. To help
you decide which SCL commands to use for an Excel automation object,
you can use the Macro Recorder in Excel to perform the task you want to
automate and then look at the Visual Basic code that is generated.  It
is then relatively simple to map the Visual Basic code to comparable
SCL statements and functions.
Here are some excerpts of Visual Basic code with their SCL equivalents:

Visual Basic code        OLE Automation in SCL
Launch Excel and make it visibleSet excelobj = CreateObject
("Excel.Application.5")
excelobj.Visible = True

hostcl = loadclass('sashelp.fsp.hauto');
call send ( hostcl,  '_NEW_', excelobj, 0,
   'Excel.Application.5');
call send (excelobj,'_SET_PROPERTY_',
   'Visible','True');

Create a new worksheetDim wbsobj, wsobj As Object
Set wbsobj = excelobj.Workbooks
wbsobj.Add
Set wsobj = excelobj.ActiveSheet
        call send(excelobj,'_GET_PROPERTY_',
   'Workbooks', wbsobj);
call send(wbsobj,  '_DO_', 'Add');
call send(excelobj,'_GET_PROPERTY_',
   'ActiveSheet', wsobj );

Set the value of a cellwsobj.Cells(row + 1, col).Value = var    r=row+1;
call send(wsobj,'_COMPUTE_', 'Cells', r, col,
   retcell);
call send(retcell,'_SET_PROPERTY_',
   'Value' ,var);
Close the Excel application objectexcelobj.ActiveWorkbook.Close
("False")
excelobj.Quit   call send(excelobj,'_GET_PROPERTY_',
   'ActiveWorkbook', awbobj);
call send(awbobj, '_DO_', 'Close',

Copyright (c) 1996, SAS Institute Inc., Cary, NC 27513-2414 USA.  All
rights reserved.

In article <8qur50$ghhrs$1@ID-25433.news.cis.dfn.de>,
  "Sebastian" <einer.einer@gmx.net> wrote:
> Hi,
>
> i need to export data to an excel-file, how can i write into the
second,
> third, ... worksheet in the file? I did not find any useful option...
>
> Regards
> Sebastian
>
>


Sent via Deja.com http://www.deja.com/
Before you buy.





--

Diese E-Mail enthält vertrauliche und/oder rechtlich geschützte Informationen. Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben, informieren Sie bitte sofort den Absender und vernichten Sie diese Mail. Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail ist nicht gestattet.

This e-mail may contain confidential and/or privileged information. If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and destroy this e-mail. Any unauthorised copying, disclosure or distribution of the material in this e-mail is strictly forbidden.