Re: EXCEL libname engine update existing sheet.

The question is about the excel libname engine, I don't see how this
is relevant.

Although you may have broken your record for the longest irrelevant reply.

On 4/1/09, Joe Whitehurst <joewhitehurst@gmail.com> wrote:
> And datanull, if you worked for enlightened management and had SAS/AF
> licensed, you could always use OLE which is far better than DDE because it
> allows you to completely control any microsoft application completely from
> within SAS Component Language--using just a few SAS Component Language
> methods. So wrting to or reading from a range or even a particular cell in
> Excel or a particular line and page within Word (using any measurement
> system supported by Word, e.g., in, cm, pct, etc.) is just as easy as
> reading from or writing to a simple text file. except you have exact,
> precise control over exactly where you will read from or write to. In
> addition, you can also insert or extract a graphic from a precise location
> in either Excel or Word.
>
>
> Using OLE Custom Controls (OCXs) in Your SAS/AF Application
>
> An OLE custom control is a special type of OLE object or collection of OLE
> objects that has an interface to expose its own properties and methods. You
> can control these objects through its graphical interface and with SCL code.
>
> OLE custom controls differ from other OLE objects in these ways:
>
> -
>
> They generate events based on user actions, which you can respond to in
> your FRAME entry. Note that the object's SCL label is not run by default
> when you activate an OLE control.
> -
>
> They assume ambient properties (such as color and font) based on the
> environment in which they are used.
>
> OLE controls are packaged in their own dynamic linked library (with a file
> extension of OCX). Using SCL code, your FRAME entry can respond to events
> generated by the OLE control (mouse clicks, key presses, and so on). The
> events exposed by OLE controls vary among controls. For a list of events,
> see the documentation for the control you are using. After inserting the
> control into the FRAME entry, you can view the event map by selecting Object
> Attributes for the OLE control object and then Event Map.
>
> Note: The OLE controls that SAS provides require 32-bit containers, which
> makes them unusable with Windows applications that offer only 16-bit
> container support. Also, because SAS is a 32-bit container, you cannot use
> 16-bit controls with it. [image: [cautionend]]
>
>
>
> -
>
> Inserting an OLE Control in a FRAME
> Entry<http://support.sas.com/documentation/cdl/en/hostwin/61924/HTML/default/ocxuse.htm#insertocx>
> -
>
> Registering OLE
> Controls<http://support.sas.com/documentation/cdl/en/hostwin/61924/HTML/default/ocxuse.htm#ocxreg>
> -
>
> Accessing OLE Control
> Properties<http://support.sas.com/documentation/cdl/en/hostwin/61924/HTML/default/ocxuse.htm#olepropacc>
> -
>
> Interacting with the OLE Control Using SCL
> Methods<http://support.sas.com/documentation/cdl/en/hostwin/61924/HTML/default/ocxuse.htm#interactoleocx>
> -
>
> Responding to OLE Control
> Events<http://support.sas.com/documentation/cdl/en/hostwin/61924/HTML/default/ocxuse.htm#oleevents>
>
> ------------------------------
> Inserting an OLE Control in a FRAME Entry
>
> To insert an OLE control in a FRAME entry:
>
> 1.
>
> From the COMPONENTS window, select the V6 objects item to expand the
> object tree.
> 2.
>
> Double-click on OLE - Insert Object from the Selection List. The Insert
> Object dialog box opens. You can also drag OLE - Insert Object from the
> Selection List to the BUILD window. When you release the mouse button the
> Insert Object dialog box opens.
> 3.
>
> Select the Create Control radio button to display a list of registered
> OLE custom controls. If the OLE control you want to use is not listed here
> and you have it on your system, you need to register the control
> (see Registering
> OLE Controls<http://support.sas.com/documentation/cdl/en/hostwin/61924/HTML/default/ocxuse.htm#ocxreg>
> ).
> 4.
>
> Select the name of the OCX control you want to insert.
>
> ------------------------------
> Registering OLE Controls
>
> Before you can use any OLE control in Windows, the control must be
> registered with Windows. SAS ComboBox and SAS Edit, the two OLE controls
> provided with SAS, are automatically registered when you install SAS.
>
> If you want to install other controls for use with SAS or other
> applications, you must register the control with Windows (unless the control
> was installed by a process that performed the registration for you). The OLE
> control will not be available from the Insert Object dialog box until it is
> registered.
>
> To register an OLE control:
>
> 1.
>
> Complete steps 1-4 as described in Inserting an OLE Control in a FRAME
> Entry<http://support.sas.com/documentation/cdl/en/hostwin/61924/HTML/default/ocxuse.htm#insertocx>to
> invoke the Insert Object dialog box with the list of registered
> controls.
> 2.
>
> Click on Add Control to invoke the Browse file selection dialog box.
> 3.
>
> Use the dialog box to select the control (which usually has a file
> extension of OCX) that you want to register.
>
> When you click OK, the control is added to the list of registered
> controls in the Insert Object dialog box.
>
> ------------------------------
> Accessing OLE Control Properties
>
> OLE controls have properties that you can set or retrieve using SCL methods.
> Some controls make some of their properties available through a properties
> page, which lets you set or retrieve the data interactively.
>
> -
>
> Accessing the OLE Control Properties
> Page<http://support.sas.com/documentation/cdl/en/hostwin/61924/HTML/default/ocxuse.htm#a000119445>
> -
>
> Accessing Properties Using SCL
> Code<http://support.sas.com/documentation/cdl/en/hostwin/61924/HTML/default/ocxuse.htm#a000119446>
>
>
>
> Accessing the OLE Control Properties Page
>
> To invoke the properties page for a control, click on the right mouse button
> within the control's region in the BUILD: DISPLAY window and then select
> Properties from the pop-up menu. The properties page for the control
> appears. An Example Properties
> Page<http://support.sas.com/documentation/cdl/en/hostwin/61924/HTML/default/ocxuse.htm#ocxprop>shows
> an example of a properties page for an OLE control.
>
> OLE controls provide a Properties verb, which you can use with the _EXECUTE_
> method in SCL to bring up the Properties page for the control. Or, you can
> access the pop-up menu for the control, then choose the cascading menu with
> the control's name. The Properties verb is available off that cascading
> menu.
>
> An Example Properties Page
>
> [image: [An Example Properties page]]
>
> You can use the properties page to view or change settings for some of the
> exposed properties.
>
> Note that the control is not active (you cannot interact with its interface)
> while you are in DISPLAY mode. The control becomes active in TESTAF mode.
>
>
>
> Accessing Properties Using SCL Code
>
> When you use OLE controls in a SAS/AF application, you can access the
> properties of the control programmatically. Also, an OLE control might not
> expose all of its properties in a properties page. You can access the
> properties of a control by using the _SET_PROPERTY_ and _GET_PROPERTY_
> methods.
>
> Before you can access a property, you must know:
>
> -
>
> the object label of the OLE control in your SAS/AF FRAME entry
> -
>
> the name of the property you want to access
> -
>
> the type of data that the property holds.
>
> For example, suppose you have a combo box control named sascombo in your
> FRAME entry, and you want to set the list style to simple (represented by
> the integer 1):
>
> call notify ('sascombo', '_set_property_',
> 'Style', 1);
>
> If you want to retrieve data from a property, you must use a variable that
> is of the same type as the data you want to read. For example, if you want
> to learn what text the user specified in the edit portion of a combo box,
> include the following code:
>
> length text $ 200;
> call notify ('sascombo', '_get_property_',
> 'Text', text);
>
> ------------------------------
> Interacting with the OLE Control Using SCL Methods
>
> OLE controls support methods that control their content and behavior. You
> use either the _DO_ or _COMPUTE_ SCL methods to send a message to an OLE
> control telling it to implement one of its methods.
>
> -
>
> Use the _DO_ method in SCL when the OLE control method performs some
> action but does not return a value. For example, the SAS ComboBox OLE
> control has a method that clears all items from the list:
>
> call notify('sascombo', '_DO_', 'Clear');
>
> -
>
> Use the _COMPUTE_ method in SCL when the OLE control method returns a
> value. You specify a variable in the SCL code that will contain the return
> value when the method ends. For example, the SAS ComboBox OLE control has a
> method that returns an item at a specified position in the list:
>
> length item $ 80;
> call notify('sascombo', '_COMPUTE_',
> 'GetItem', 2, item);
>
> When this call returns, item contains the text of the item at position 2
> (the third item in the list).
>
> ------------------------------
> Responding to OLE Control Events
>
> OLE controls generate events that you can respond to in your SCL code. You
> can create a label in your SCL code for OLE events just like you do for
> SAS/AF events.
>
> -
>
> Assigning SCL Code to an OLE Control
> Event<http://support.sas.com/documentation/cdl/en/hostwin/61924/HTML/default/ocxuse.htm#a000119447>
> -
>
> Retrieving Argument Values from
> Events<http://support.sas.com/documentation/cdl/en/hostwin/61924/HTML/default/ocxuse.htm#a000119448>
> -
>
> Example: Mapping OLE Control Events to SCL
> Code<http://support.sas.com/documentation/cdl/en/hostwin/61924/HTML/default/ocxuse.htm#a000119449>
> -
>
> Example: Subclassing an OLE Custom
> Control<http://support.sas.com/documentation/cdl/en/hostwin/61924/HTML/default/ocxuse.htm#subole>
> -
>
> Adding an Item to a Combo Box
> List<http://support.sas.com/documentation/cdl/en/hostwin/61924/HTML/default/ocxuse.htm#a000119450>
> -
>
> Finding an Item in a Combo
> Box<http://support.sas.com/documentation/cdl/en/hostwin/61924/HTML/default/ocxuse.htm#a000119451>
> -
>
> Retrieving the Text Value of the
> Control<http://support.sas.com/documentation/cdl/en/hostwin/61924/HTML/default/ocxuse.htm#a000119452>
>
>
>
> Assigning SCL Code to an OLE Control Event
>
> To assign SCL code to run when an OLE control event occurs:
>
> 1.
>
> Select the OLE control object in the BUILD window.
> 2.
>
> Select Object Attributes from the pop-up menu for the object.
> 3.
>
> Select Event Map from the Object Attributes dialog box. The Event Map
> dialog box appears (shown in Event Map Dialog
> Box<http://support.sas.com/documentation/cdl/en/hostwin/61924/HTML/default/ocxuse.htm#oleeventdlg>
> ).
> 4.
>
> In the Event Map dialog box, select the event that you want to respond to
> using SCL code.
> 5.
>
> Specify the SCL, FRAME, or PROGRAM source entry and (if applicable) the
> SCL label where the event-handling code resides.
>
> Note: You can specify the same SCL source entry that is stored with the
> FRAME entry; however, in addition to compiling the code with the FRAME
> entry, you must also compile the SCL entry outside of the FRAME context
> (outside of the BUILD: SOURCE and BUILD: DISPLAY windows) in order for the
> event handler to recognize the SCL label. It is more efficient to store
> event-handling code for OLE controls in an SCL source entry that is not
> associated with a FRAME entry. [image: [cautionend]]
>
>
>
> Event Map Dialog Box
>
> [image: [The Event Map dialog box]]
>
> Note: Many OLE controls include a LostFocus event, which they generate
> when the control loses window focus. Because of the way that SAS/AF software
> communicates with the control, mapping the LostFocus event sometimes has the
> effect of placing focus back on the control that just lost it. Although you
> can still respond to the LostFocus event in your FRAME entry, this action
> might cause unusual focus behavior. [image: [cautionend]]
>
>
>
> Retrieving Argument Values from Events
>
> Some OLE control events also include parameters you might find useful. For
> example, the SAS ComboBox control generates a KeyPress event that also
> reports the ASCII value of the key that was pressed. If a particular event
> passes an argument back to the FRAME entry, the type of value returned is
> indicated in the Event Map dialog box. A numeric value is indicated with an
> N ; a character value is indicated with a C .
>
> To retrieve the value returned by an OLE control event, you must define a
> method (using the METHOD statement in SCL) in the event-handling code. In
> the argument list for the METHOD statement, specify a variable of the type
> that you expect the OLE control to return. This variable contains the value
> returned by the event. You can then use that variable inside your
> event-handler.
>
> For example, suppose you want to retrieve the value of the key that
> triggered the KeyPress event in the SAS ComboBox control and then report it
> as an ASCII character. The KeyPress event returns an integer that represents
> the ASCII value of the key pressed. Your event-handling code would look like
> the following:
>
> /* Label specified in Event Map dialog box */
> KEYPRESS:
> /* Define a method with an
> integer argument */
> method keyval 8;
> /* Convert the integer to an
> ASCII character */
> keychar=byte(keyval);
> put keychar=; /* Output the character */
> endmethod;
>
>
>
> Example: Mapping OLE Control Events to SCL Code
>
> When mapping OLE control events, you can do one of the following:
>
> -
>
> Map each event in the Event Map window to a different labeled section of
> SCL code, with each piece of code performing different actions.
> -
>
> Map all of the events in the Event Map window to a single labeled section
> of SCL code, use the _GET_EVENT_ method to detect which event was triggered,
> and act accordingly.
> -
>
> Use a combination of these strategies by assigning one event (such as the
> Click event) to SCL code that runs the object's label (using the
> _OBJECT_LABEL_ method), and map the remaining events to a single label that
> uses the _GET_EVENT_ method to determine the event and appropriate action.
> The object's label is not run by default for OLE controls.
>
> The following example shows how to structure the SCL code when all events
> for an OLE control are mapped to a single label, which in turn runs the
> object's label to determine the event and act accordingly:
>
> length event $ 80;
> /* All OLE control events are mapped to
> this label */
> RUNLABEL:
> /* Call the object's label */
> call send(_self_, '_OBJECT_LABEL_');
> return;
> /* This is the label of the OLE control */
> OBJ1:
> /* Determine the last event */
> call notify('obj1', '_GET_EVENT_', event);
> select (event);
> when('Click') put 'Click received';
> when('DblClick') put 'DblClick received';
> otherwise put event=;
> end;
> return;
>
>
>
> Example: Subclassing an OLE Custom Control
>
> If you create SAS/AF applications that make frequent use of one or more OLE
> custom controls, you might want to write your own methods to abstract the
> methods that the control recognizes without having to specify the
> intermediate _DO_ and _COMPUTE_ methods in SCL.
>
> You can write methods by creating a subclass of the OLE class and adding
> methods to your derived class. When you insert the OLE control into your
> FRAME entry, be sure to insert it as an instance of the new class that you
> define (instead of OLE - Insert Object). The examples provided here contain
> sample code you can use to abstract the methods of a control. They do not
> include details about how to create subclasses. For information about
> creating subclasses of a SAS/AF class, see the online documentation for
> SAS/AF software.
>
>
>
> Adding an Item to a Combo Box List
>
> You can use this method to add a new item to the list portion of the SAS
> ComboBox control. The SAS ComboBox control uses zero-based numbering to
> indicate the positions of the list items (the first item is at position 0,
> the second is at position 1, and so on). The following method lets you
> specify the position numbers such that position 1 holds the first item.
>
> /* Add a new item to a ComboBox list. */
> ADDITEM:
> method text $200 row 8 rc 8;
> /* adjust for zero-based index */
> ocxrow = row-1;
> call send(_self_, '_COMPUTE_', 'AddItem',
> text, ocxrow, rc);
> if ( rc = 0 ) then
> _MSG_="ERROR: Could not add item to list.";
> endmethod;
>
> Assuming you mapped this code to a new method called ADD_ITEM, you would
> use this syntax to add a new item to the control:
>
> /* Adds 'Item 1' at the first position */
> /* in the control */
> length success 8;
> call notify('sascombo', 'ADD_ITEM',
> 'Item 1', 1, success);
>
>
>
> Finding an Item in a Combo Box
>
> The following method finds the specified item and returns its position in
> the list. As in the previous example, this method adjusts the position
> number to be one-based instead of zero-based.
>
> FINDITEM:
> method text $200 row 8;
> call send(_self_, '_COMPUTE_', 'FindItem',
> text, row);
> row = row + 1; /* adjust for zero-based */
> endmethod; /* index */
>
> Assuming you mapped this code to the FIND_ITEM method, you would then use
> it as in this example:
>
> length position 8;
> call notify('sascombo','FIND_ITEM',
> 'Lost Item', position);
>
>
>
> Retrieving the Text Value of the Control
>
> Both the SAS ComboBox and SAS Edit controls have Text properties, which you
> can access using the _GET_PROPERTY_ method with the property name. For
> easier and more intuitive access from your OLE subclass, you can override
> the _GET_TEXT_ method and map it to this code:
>
> GETTEXT:
> method text $200;
> call send(_self_, '_GET_PROPERTY_',
> 'Text', text);
> endmethod;
>
> You would then access the Text property of a control the same way you
> access the text of other SAS/AF widget objects:
>
> length text $ 200;
> call notify('sasedit', '_GET_TEXT_', text);
>
> Previous
> Page<http://support.sas.com/documentation/cdl/en/hostwin/61924/HTML/default/oleautoapp.htm>
> | Next Page<http://support.sas.com/documentation/cdl/en/hostwin/61924/HTML/default/introoleauto.htm>
> | Top of Page<http://support.sas.com/documentation/cdl/en/hostwin/61924/HTML/default/ocxuse.htm#ocxuse>
>
> Copyright © 2008 by SAS Institute Inc., Cary, NC, USA. All rights
> reserved.<http://support.sas.com/documentation/cdl/en/common/61991/HTML/default/images/copyrite.htm>
>
>
> - Previous Page
> <http://support.sas.com/documentation/cdl/en/hostwin/61924/HTML/default/oleautoapp.htm>
> - |
> - Next Page
> <http://support.sas.com/documentation/cdl/en/hostwin/61924/HTML/default/oleauto.htm>
> - |
> - Top of Page<http://support.sas.com/documentation/cdl/en/hostwin/61924/HTML/default/ocxuse.htm#content>
>
> Contact Us <http://support.sas.com/contact/index.html> |
> Sitemap<http://support.sas.com/sitemap.html>| RSS
> Feeds <http://support.sas.com/community/rss/> | www.sas.com | Terms of Use &
> Legal Information <http://www.sas.com/Copyright.html> | Privacy
> Statement<http://www.sas.com/Privacy.html>
> Copyright © 2009 SAS Institute Inc. All Rights Reserved. [image:
> invitation popup window for live chat with an online
> representative]<http://support.sas.com/documentation/cdl/en/hostwin/61924/HTML/default/ocxuse.htm#>
> [image:
> Close Chat Invitation]<http://support.sas.com/documentation/cdl/en/hostwin/61924/HTML/default/ocxuse.htm#>
>
>
>
> On 4/1/09, Arthur Tabachneck <art297@netscape.net> wrote:
> >
> > datanull,
> >
> > There's always DDE to a specific range.
> >
> > Art
> > ---------
> > On Wed, 1 Apr 2009 13:51:11 -0500, ./ ADD NAME=Data _null_;
> > <iebupdte@GMAIL.COM> wrote:
> >
> > >PROC APPEND.
> > >
> > >Any others?
> > >
> > >On 4/1/09, ./ ADD NAME=Data _null_; <iebupdte@gmail.com> wrote:
> > >> What SAS statements can I use to write observations to and existing
> > >> sheet. e.g. ex.'Sheet1$'n.
> > >>
> > >> I can use SQL; INSERT INTO but I would like to know other alternatives
> > >> if they exist.
> > >>
> >
>