This discussion is archived
10 Replies Latest reply: Jan 16, 2013 12:45 PM by 619741 RSS

Paste clipboard content into a multiline block (Forms 6i, EBS 11.5.9)

Jose Aróstegui Explorer
Currently Being Moderated
Hi everyone!

I'd like to paste the content of the clipboard into a multiline block in forms.

The problem is that we're using forms6i in a EBS 11.5.9 web environment.

My main goal is:

1) Select several values with the mouse from an Excel sheet.
2) Copy them (CTRL+C)
3) Paste all the values into a multine block of a custom form in the EBS application.

I've achieved to create a pop up menu with a paste option, but I only get 1 value pasted at a time!!

Thanks a lot in advance!!
Jose.
  • 1. Re: Paste clipboard content into a multiline block (Forms 6i, EBS 11.5.9)
    Jose Aróstegui Explorer
    Currently Being Moderated
    Hi! Any clue about this issue??

    Thanks.
  • 2. Re: Paste clipboard content into a multiline block (Forms 6i, EBS 11.5.9)
    FrançoisDegrelle Oracle ACE
    Currently Being Moderated
    What you can do, is to paste in a multi-line TextItem, then take any token from it and paste them in the multi-record table in a loop.

    Francois
  • 3. Re: Paste clipboard content into a multiline block (Forms 6i, EBS 11.5.9)
    Jose Aróstegui Explorer
    Currently Being Moderated
    Hi!

    It's my intention, but when I try to paste the content of the Clipboard into an Item (hidden or not) I get FRM-40218 when I use procedure paste_region!! :-(
  • 4. Re: Paste clipboard content into a multiline block (Forms 6i, EBS 11.5.9)
    FrançoisDegrelle Oracle ACE
    Currently Being Moderated
    Maybe because the clipboard don't contain single text data.

    Francois
  • 5. Re: Paste clipboard content into a multiline block (Forms 6i, EBS 11.5.9)
    FrançoisDegrelle Oracle ACE
    Currently Being Moderated
    I have just made the test. created a form with a multi-line text item (4000 char length), created an Excel file with a column filled from 1 to 30 (A1 -> A30), copied the cells, then pasted them in the TextItem without any problem.

    Francois
  • 6. Re: Paste clipboard content into a multiline block (Forms 6i, EBS 11.5.9)
    Arif Khadas Expert
    Currently Being Moderated
    Hi,

    If you need to import loads of data from Excel sheet you can open the Excel file through forms and import all the cell values from the Excel sheet into the respective fields.

    The following code might help you :


    DECLARE
         app                                        OLE2.OBJ_TYPE;
         workbooks                         OLE2.OBJ_TYPE;
         workbook                         OLE2.OBJ_TYPE;
         worksheets                    OLE2.OBJ_TYPE;
         worksheet                     OLE2.OBJ_TYPE;
         cell                                    OLE2.OBJ_TYPE;
         args                                    OLE2.OBJ_TYPE;
         ctr                                    NUMBER(12);
         V_VALUE                          VARCHAR2(100);
         --rec                                        CALC_ROTANA%ROWTYPE;
         V_FILENAME                    VARCHAR2(256);
    BEGIN
         V_FILENAME     := GET_FILE_NAME(File_Filter=> 'XLS Files (*.XLS)|');
         IF V_FILENAME IS NULL THEN
              RAISE FORM_TRIGGER_FAILURE;
         END IF;     
         SET_APPLICATION_PROPERTY (CURSOR_STYLE,'BUSY');
         app := OLE2.CREATE_OBJ('Excel.Application');
         ole2.set_property(app,'Visible','false');
         workbooks := OLE2.GET_OBJ_PROPERTY(app, 'Workbooks');
         
         args := OLE2.CREATE_ARGLIST;
         ole2.add_arg(args,V_FILENAME);
         
         workbook := ole2.GET_OBJ_PROPERTY(workbooks,'Open',args);

         ole2.destroy_arglist(args);
         
         args := OLE2.CREATE_ARGLIST;
         
         ole2.add_arg(args,'calc_rotana'); --CONFIG_STRUCTURE');

         worksheet := ole2.GET_OBJ_PROPERTY(workbook,'Worksheets',args);
         ole2.destroy_arglist(args);
         
         ctr := 2;

         FIRST_RECORD;

         LOOP
              :BL_IMPORT_CONFIG_STRUCTURE.COMPONENT_PART := GET_CELL_VALUE(WORKSHEET,     ARGS,     CELL, CTR, 1);
              OLE2.RELEASE_OBJ(cell);

              :BL_IMPORT_CONFIG_STRUCTURE.QTY_PER_ASSEMBLY := GET_CELL_VALUE(WORKSHEET,     ARGS,     CELL, CTR, 2);      
              OLE2.RELEASE_OBJ(cell);
              
              /*
              :BL_IMPORT_CONFIG_STRUCTURE.ISSUE_TO_LOC := GET_CELL_VALUE(WORKSHEET,     ARGS,     CELL, CTR, 3);
              OLE2.RELEASE_OBJ(cell);
              */
              
              NEXT_RECORD;
              
              V_VALUE     := GET_CELL_VALUE(WORKSHEET,     ARGS,     CELL, CTR, 1);
              OLE2.RELEASE_OBJ(cell);
         
              EXIT WHEN LENGTH (V_VALUE) = 0 OR V_VALUE IS NULL;
              CTR := CTR + 1;
         END LOOP;

         OLE2.INVOKE(app,'Quit');
         OLE2.RELEASE_OBJ(cell);
         OLE2.RELEASE_OBJ(worksheet);
         --OLE2.RELEASE_OBJ(worksheets);
         OLE2.RELEASE_OBJ(workbook);
         OLE2.RELEASE_OBJ(workbooks);
         OLE2.RELEASE_OBJ(app);
         SET_APPLICATION_PROPERTY (CURSOR_STYLE,'NORMAL');

         SET_ITEM_PROPERTY ('BL_IMPORT_CONFIG_STRUCTURE.PB_CREATE_CONFIG_STRUCTURE',ENABLED,PROPERTY_TRUE);
         GO_ITEM ('BL_IMPORT_CONFIG_STRUCTURE.PB_CREATE_CONFIG_STRUCTURE');
         SET_ITEM_PROPERTY ('BL_IMPORT_CONFIG_STRUCTURE.PB_IMPORT_CONFIG_STRUCT_FILE',ENABLED,PROPERTY_FALSE);

         EXCEPTION
              WHEN OTHERS THEN
                   OLE2.INVOKE(app,'Quit');
                   OLE2.RELEASE_OBJ(cell);
                   OLE2.RELEASE_OBJ(worksheet);
                   --OLE2.RELEASE_OBJ(worksheets);
                   OLE2.RELEASE_OBJ(workbook);
                   OLE2.RELEASE_OBJ(workbooks);
                   OLE2.RELEASE_OBJ(app);
                   SET_APPLICATION_PROPERTY (CURSOR_STYLE,'NORMAL');
                   SET_ITEM_PROPERTY ('BL_IMPORT_CONFIG_STRUCTURE.PB_CREATE_CONFIG_STRUCTURE',ENABLED,PROPERTY_TRUE);
                   GO_ITEM ('BL_IMPORT_CONFIG_STRUCTURE.PB_CREATE_CONFIG_STRUCTURE');
                   SET_ITEM_PROPERTY ('BL_IMPORT_CONFIG_STRUCTURE.PB_IMPORT_CONFIG_STRUCT_FILE',ENABLED,PROPERTY_FALSE);
    END;

    Best Regards

    Arif Khadas
  • 7. Re: Paste clipboard content into a multiline block (Forms 6i, EBS 11.5.9)
    Jose Aróstegui Explorer
    Currently Being Moderated
    Hi Francois,

    Your right, but I'm trying to use the procedure paste_region, as I don't want to display a big text area just to paste the text.

    My intention is to have a button which pastes the content of the clipboard into a non displayed text item, and after that, process the lines, parse the separator character, and move the data to a multi-line block with a for-loop.

    So, the user would select and copy several cells (10-15) in an Excel sheet, and after that he'd click a new button in the forms which would paste the cells into the multi-line block. I hope I made myself clear about this.

    Thanks!!!
    -----------------

    Hi Arif,

    Thanks about your suggestion, but I can't use OLE2 as we're in Jinitiator Web environment of the EBS 11.5.9.

    Really apreciate your help anyway!
  • 8. Re: Paste clipboard content into a multiline block (Forms 6i, EBS 11.5.9)
    FrançoisDegrelle Oracle ACE
    Currently Being Moderated
    If you want to paste into it, the multi-line text item must be visible. What you can do is to set its size to 1x1 pixel. Then, in you button, procedd as follow:
    Declare
      c varchar2(4000);
    Begin     
      go_item('bl.t1'); -- 1*1 pixel text item
      paste_region;
      -- then take parts of the multi-line text --
      c := split(:bl.t1,1,CHR(10));
      :bl.t2 := c ; -- to put the token in each record text item
    End;
  • 9. Re: Paste clipboard content into a multiline block (Forms 6i, EBS 11.5.9)
    FrançoisDegrelle Oracle ACE
    Currently Being Moderated
    This is working for me:
    Declare
         c varchar2(4000);
         i pls_integer := 1 ;
    Begin     
      go_item('bl.t1'); -- 1*1 pixel item to paste
      paste_region;
      -- loop through records --
      go_block('bl2');
      loop
        c := split(:bl.t1,i,CHR(10));
        exit when c is null ;
        if :system.record_status <> 'NEW' Then
              create_record ;
        end if ;      
        :bl2.ti := c ;
        i := i + 1 ;
      end loop;
    End;
    the SPLIT() function:
      --------------------------
      --  get a string token  --
      --------------------------
         Function Split
         (
            PC$Chaine IN VARCHAR2,         -- input string
            PN$Pos IN PLS_INTEGER,         -- token number
            PC$Sep IN VARCHAR2 DEFAULT ',' -- separator character
         )
         RETURN VARCHAR2
         IS
           LC$Chaine VARCHAR2(32767) := PC$Sep || PC$Chaine ;
           LI$I      PLS_INTEGER ;
           LI$I2     PLS_INTEGER ;
         BEGIN
           LI$I := INSTR( LC$Chaine, PC$Sep, 1, PN$Pos ) ;
           IF LI$I > 0 THEN
             LI$I2 := INSTR( LC$Chaine, PC$Sep, 1, PN$Pos + 1) ;
             IF LI$I2 = 0 THEN LI$I2 := LENGTH( LC$Chaine ) + 1 ; END IF ;
             RETURN( Nvl(SUBSTR( LC$Chaine, LI$I+1, LI$I2 - LI$I-1 ),'^') ) ;
           ELSE
             RETURN NULL ;
           END IF ;
         END Split;
    Francois
  • 10. Re: Paste clipboard content into a multiline block (Forms 6i, EBS 11.5.9)
    619741 Newbie
    Currently Being Moderated
    Francois - I used your code to copy excel values into multi-line form columns. I get the sting '^' copied inot the first field. Is there something wrong that I am doing here?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points