Re: little OLE automation with Excel 97 and Forms 4.5

From: Patrick Hamou <patrick_hamou_at_hotmail.com>
Date: 2000/03/25
Message-ID: <AFbD4.17250$1C2.464787@news20.bellglobal.com>#1/1

heres the forms code to navigate through a Forms grid and send the information into a Excel document..

good luck !

patrick hamou
Oracle DBA
patrickh_at_sympatico.ca

ps : the comments are in french...

PROCEDURE TRANSFERT(X NUMBER := 1, Y NUMBER := 1,    NOM_BLOCK VARCHAR2 := 'V_TABLEAU_BUDGET_GL',    NOM_FICHIER VARCHAR2 := 'H:\RAPPORT\EXEMPLE.XLS') IS MyApplication 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.LIST_TYPE;

nCOMPTEUR_COLONNE NUMBER := X;
nCOMPTEUR_LIGNE NUMBER := Y;

cPREMIER VARCHAR2(100);

BEGIN GO_BLOCK(NOM_BLOCK); LAST_RECORD; IF GET_BLOCK_PROPERTY(NOM_BLOCK, CURRENT_RECORD) >= 1 THEN  GO_RECORD(1);  MyApplication:=OLE2.CREATE_OBJ('Excel.Application');  OLE2.SET_PROPERTY(MyApplication, 'Visible', 1);

 workbooks:=OLE2.GET_OBJ_PROPERTY(MYapplication, 'Workbooks');  workbook:=OLE2.INVOKE_OBJ(workbooks,'Add');

 worksheets:=OLE2.GET_OBJ_PROPERTY(workbook, 'Worksheets');  worksheet:=OLE2.INVOKE_OBJ(worksheets,'Add');

--ENVOYER LES LABELS
 nCOMPTEUR_COLONNE := X;

 cPREMIER := :SYSTEM.CURRENT_ITEM;
 LOOP
  IF GET_ITEM_PROPERTY(:SYSTEM.CURRENT_BLOCK || '.' || :SYSTEM.CURRENT_ITEM, VISIBLE) = 'TRUE' AND
     GET_ITEM_PROPERTY(:SYSTEM.CURRENT_BLOCK || '.' || :SYSTEM.CURRENT_ITEM, ITEM_TYPE) IN('TEXT ITEM', 'LIST') THEN
   args:=OLE2.CREATE_ARGLIST;
   OLE2.ADD_ARG(args, nCOMPTEUR_LIGNE);
   OLE2.ADD_ARG(args, nCOMPTEUR_COLONNE);    cell:=OLE2.GET_OBJ_PROPERTY(worksheet, 'Cells', args);    OLE2.DESTROY_ARGLIST(args);

• OLE2.SET_PROPERTY(cell, 'Selection.Font.Bold', 1);

   OLE2.SET_PROPERTY(cell, 'Value', GET_ITEM_PROPERTY(:SYSTEM.CURRENT_BLOCK || '.' || :SYSTEM.CURRENT_ITEM, ITEM_NAME));   END IF;   nCOMPTEUR_COLONNE := nCOMPTEUR_COLONNE + 1;   NEXT_ITEM;
  EXIT WHEN cPREMIER = :SYSTEM.CURRENT_ITEM;  END LOOP;  nCOMPTEUR_LIGNE := nCOMPTEUR_LIGNE + 1;  GO_RECORD(1); --ENVOYER LES DONNÉES
 LOOP
  EXIT WHEN :SYSTEM.CURRENT_VALUE IS NULL;   nCOMPTEUR_COLONNE := X;

• GO_ITEM(1); --EN COMMENTAIRE AU CAS OU LE PREMIER N'EST PAS NAVIGABLE...(PAR DÉFAULT SUR LE GO_BLOCK ANYWAYS)

  cPREMIER := :SYSTEM.CURRENT_ITEM;
  LOOP
   IF GET_ITEM_PROPERTY(:SYSTEM.CURRENT_BLOCK || '.' || :SYSTEM.CURRENT_ITEM, VISIBLE) = 'TRUE' AND       GET_ITEM_PROPERTY(:SYSTEM.CURRENT_BLOCK || '.' || :SYSTEM.CURRENT_ITEM, ITEM_TYPE ) IN('TEXT ITEM', 'LIST') THEN     args:=OLE2.CREATE_ARGLIST;
    OLE2.ADD_ARG(args, nCOMPTEUR_LIGNE);     OLE2.ADD_ARG(args, nCOMPTEUR_COLONNE);     cell:=OLE2.GET_OBJ_PROPERTY(worksheet, 'Cells', args);     OLE2.DESTROY_ARGLIST(args);

    OLE2.SET_PROPERTY(cell, 'Value', :SYSTEM.CURRENT_VALUE);    END IF;    nCOMPTEUR_COLONNE := nCOMPTEUR_COLONNE + 1;    NEXT_ITEM;
   EXIT WHEN cPREMIER = :SYSTEM.CURRENT_ITEM;   END LOOP;   nCOMPTEUR_LIGNE := nCOMPTEUR_LIGNE + 1;   NEXT_RECORD;
 END LOOP;  args:=OLE2.CREATE_ARGLIST;

 OLE2.ADD_ARG(args, NOM_FICHIER);
 OLE2.INVOKE(workbook, 'SaveAs', args);
 OLE2.DESTROY_ARGLIST(args);

 OLE2.INVOKE(workbook, 'Close');
 OLE2.INVOKE(MYapplication, 'Quit');

 OLE2.RELEASE_OBJ(cell);
 OLE2.RELEASE_OBJ(worksheet);
 OLE2.RELEASE_OBJ(worksheets);
 OLE2.RELEASE_OBJ(workbook);
 OLE2.RELEASE_OBJ(workbooks);
 OLE2.RELEASE_OBJ(MyApplication);

 GO_RECORD(1);
ELSE
 AFF_MSG('E', 'Vous devez faire une requête en premier'); END IF; EXCEPTION
 WHEN OTHERS THEN
  AFFICHER_EXCEPTION('TRANSFERT OLE');
END; Nicolas Dezaire <ndezaire_at_theleme.com> wrote in message news:8bd2ai$be8$1_at_wanadoo.fr...
> Hi I just wanna open excel 97 and write data in some cells from Froms 4.5.
> I open Excel but it doesn't understant what i mean after...
>
> The doc I found tells to do something like that :
>
> application := ole2.Create_Obj('Excel.Application');
> ole2.Set_Property(application, 'Visible', 'TRUE');
> classeurs := ole2.Invoke_obj(application, 'Workbooks');
> classeur := ole2.Invoke_obj(classeurs, 'Add');
>
> I work with a french version of Excel so I must put VRAI instead of
> TRUE but others lines make error.
>
> Can help me ?
>
> Thanks
>
> Nico
>
>
>
Received on Sat Mar 25 2000 - 00:00:00 CST