Re: little OLE automation with Excel 97 and Forms 4.5
Date: Sat, 25 Mar 2000 23:08:16 GMT
Message-ID: <AFbD4.17250$1C2.464787_at_news20.bellglobal.com>
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)
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 Sun Mar 26 2000 - 00:08:16 CET