OLE2 / Excel / Oracle Forms 3

[mashtiani]

The following code causes an error on second line, Windows 2000/XP with Office 2000/XP. with Excel.Application.9 & Excel.Application.10 I am not sure of the others. is there anything I do wrong here ?

declare
application OLE2.OBJ_TYPE;
workbook OLE2.OBJ_TYPE;

workbooks OLE2.OBJ_TYPE;
worksheet OLE2.OBJ_TYPE;
worksheets OLE2.OBJ_TYPE;
cell OLE2.OBJ_TYPE;
args OLE2.List_Type;
begin

application:=OLE2.CREATE_OBJ('Excel.Application');
workbooks:=OLE2.INVOKE_OBJ(application, 'Workbooks');
OLE2.Set_Property(application,'Visible', 'True');
workbook:=OLE2.INVOKE_OBJ(workbooks,'Add');
worksheets:=OLE2.INVOKE_OBJ(workbook, 'Worksheets');
worksheet:=OLE2.INVOKE_OBJ(worksheets,'Add');
args:=OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, 4);
OLE2.ADD_ARG(args, 2);
cell:=OLE2.Invoke_Obj(worksheet, 'Cells', args);
OLE2.DESTROY_ARGLIST(args);
OLE2.Set_Property(cell, 'Value', 'Hello Excel!');
OLE2.Release_Obj( application );

exception
when others then
OLE2.Release_Obj( application );
message('errors');
end;

 

[sfvb]

Try this:

application:=OLE2.CREATE_OBJ('Excel.Application');
OLE2.SET_PROPERTY(application, 'Visible', 'True');
workbooks := OLE2.GET_OBJ_PROPERTY(application, 'Workbooks');
workbook := OLE2.INVOKE_OBJ(workbooks,'Add');
worksheets := OLE2.GET_OBJ_PROPERTY(workbook, 'Worksheets');
worksheet := OLE2.INVOKE_OBJ(worksheets,'Add');
args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, 4);
OLE2.ADD_ARG(args, 2);
cell := OLE2.GET_OBJ_PROPERTY(worksheet, 'Cells', args);
OLE2.DESTROY_ARGLIST(args);
OLE2.SET_PROPERTY(cell, 'Value', 'Hello Excel!');
OLE2.RELEASE_OBJ(cell);
OLE2.RELEASE_OBJ(worksheet);
OLE2.RELEASE_OBJ(worksheets);
OLE2.RELEASE_OBJ(workbook);
OLE2.RELEASE_OBJ(workbooks);
OLE2.RELEASE_OBJ(application);

 

[mashtiani]

Thanks, the get_obj method works, however does anybody know how to set a cell width or hight and Bold/Normal Fonts?



Thanks again sfvp

 

[sfvb]

Here is a long example that shows how to set a few other things also.

DECLARE
application OLE2.OBJ_TYPE;
workbook OLE2.OBJ_TYPE;
workbooks OLE2.OBJ_TYPE;
worksheet OLE2.OBJ_TYPE;
worksheets OLE2.OBJ_TYPE;
cell OLE2.OBJ_TYPE;
column OLE2.OBJ_TYPE;
row OLE2.OBJ_TYPE;
font OLE2.OBJ_TYPE;

args OLE2.List_Type;
BEGIN
application := OLE2.CREATE_OBJ('Excel.Application');
OLE2.SET_PROPERTY(application, 'Visible', 'True');
workbooks := OLE2.GET_OBJ_PROPERTY(application, 'Workbooks');
workbook := OLE2.INVOKE_OBJ(workbooks,'Add');

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

--Set default for columns F, G and H
args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, 'F:H'); --Columns F thru H
column := OLE2.GET_OBJ_PROPERTY(worksheet, 'Columns', args);
OLE2.DESTROY_ARGLIST(args);
font := OLE2.GET_OBJ_PROPERTY(column, 'Font', args);
OLE2.SET_PROPERTY(font, 'Name', 'Courier New');
OLE2.SET_PROPERTY(font, 'Size', 12);
OLE2.SET_PROPERTY(font, 'Strikethrough', 'True');
OLE2.SET_PROPERTY(font, 'Bold', 'True');
OLE2.SET_PROPERTY(font, 'ColorIndex', 5); --Dark Blue
OLE2.RELEASE_OBJ(font);
--Set width for columns F thru H to 25
OLE2.SET_PROPERTY(column, 'ColumnWidth', 25);
OLE2.RELEASE_OBJ(column);

--Set default for rows 1 through 20
args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, '1:20'); --Rows 1 thru 20
row := OLE2.GET_OBJ_PROPERTY(worksheet, 'Rows', args);
OLE2.DESTROY_ARGLIST(args);
OLE2.SET_PROPERTY(row , 'RowHeight', 12);
OLE2.RELEASE_OBJ(row);

--Set row 4 height
args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, 4); --Row 4
row := OLE2.GET_OBJ_PROPERTY(worksheet, 'Rows', args);
OLE2.DESTROY_ARGLIST(args);
OLE2.SET_PROPERTY(row , 'RowHeight', 25);
OLE2.RELEASE_OBJ(row);

--Set cell B4
args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, 4); --Row 4
OLE2.ADD_ARG(args, 2); --Column B
cell := OLE2.GET_OBJ_PROPERTY(worksheet, 'Cells', args);
OLE2.DESTROY_ARGLIST(args);
OLE2.SET_PROPERTY(cell, 'Value', 'Hello Excel!');
font := OLE2.GET_OBJ_PROPERTY(cell, 'Font', args);
OLE2.SET_PROPERTY(font, 'Name', 'Arial');
OLE2.SET_PROPERTY(font, 'FontStyle', 'Italic');
OLE2.SET_PROPERTY(font, 'Size', 20);
-- possible Underline values = 2, 4, 5, -4119
OLE2.SET_PROPERTY(font, 'Underline', -4119);
OLE2.SET_PROPERTY(font, 'Bold', 'True');
OLE2.SET_PROPERTY(font, 'ColorIndex', 3); --Red
OLE2.RELEASE_OBJ(font);
OLE2.RELEASE_OBJ(cell);

--Set cell F4
args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, 4);
OLE2.ADD_ARG(args, 6);
cell := OLE2.GET_OBJ_PROPERTY(worksheet, 'Cells', args);
OLE2.DESTROY_ARGLIST(args);
OLE2.SET_PROPERTY(cell, 'Value', 'Good Bye Excel!');
OLE2.RELEASE_OBJ(cell);

--Set cell B7
args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, 7);
OLE2.ADD_ARG(args, 3);
cell := OLE2.GET_OBJ_PROPERTY(worksheet, 'Cells', args);
OLE2.DESTROY_ARGLIST(args);
OLE2.SET_PROPERTY(cell, 'Value', 9);
OLE2.RELEASE_OBJ(cell);

--Set cell C7
args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, 7);
OLE2.ADD_ARG(args, 'B');
cell := OLE2.GET_OBJ_PROPERTY(worksheet, 'Cells', args);
OLE2.DESTROY_ARGLIST(args);
OLE2.SET_PROPERTY(cell, 'Value', 4);
OLE2.RELEASE_OBJ(cell);

--Set formula for cell D7
args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, '7');
OLE2.ADD_ARG(args, 'D');
cell := OLE2.GET_OBJ_PROPERTY(worksheet, 'Cells', args);
OLE2.DESTROY_ARGLIST(args);
OLE2.SET_PROPERTY(cell, 'Formula', '=B7*C7');
OLE2.RELEASE_OBJ(cell);

OLE2.RELEASE_OBJ(worksheet);
OLE2.RELEASE_OBJ(worksheets);
OLE2.RELEASE_OBJ(workbook);
OLE2.RELEASE_OBJ(workbooks);
OLE2.RELEASE_OBJ(application);
EXCEPTION
WHEN others THEN
OLE2.Release_Obj( application );
message('Error');
END;

 

[sfvb]

The comments "--Set cell B7" and "--Set cell C7" should be switched.

 

[jhoann]

hi sfvb,

I'm having problem with OLE2.... I read your codes above... I want to try it but I don't know where to put/write those codes... can you please help me .. I'm just a starter in OLE2...

Thanks in advance!


Jhoann

 

[jhoann]

Hi sfvb,

It's me again... I got it already... thanks ...

how about using microsoft Word... can it also be done having a database ??? I hope you can also help me with this one.....

Thanks in advance!