ORACLE / EXCEL using OLE2 2

[jhoann]

hi,

i have a code here but it dont run correctly... can somebody help me check what's wrong with my code....

it will be a great help... thanks.


DECLARE

-- Declare handles to OLE objects
application ole2.obj_type;
workbooks ole2.obj_type;
workbook ole2.obj_type;
worksheet ole2.obj_type;
worksheets ole2.obj_type;
cell ole2.obj_type;

-- Declare handles to OLE argument lists
args ole2.list_type;



BEGIN


application:= ole2.create_obj('Excel.Application');
workbooks := ole2.invoke_obj(application, 'Workbooks');

-- Open the required workbook
args:= ole2.create_arglist;
ole2.add_arg(args, 'C:\TEST\EXAMPLE.XLS');
workbook := ole2.invoke_obj(workbooks, 'Open', args);
ole2.destroy_arglist(args);

-- Open worksheet Sheet1 of that Workbook
args:= ole2.create_arglist;
ole2.add_arg(args, 'Sheet1');
worksheet := ole2.invoke_obj(workbook, 'Worksheets', args);
ole2.destroy_arglist(args);

-- Get value of cell A1 of worksheet Sheet1
args:= ole2.create_arglist;
ole2.add_arg(args, 1);
ole2.add_arg(args, 1);
cell:= ole2.invoke_obj(worksheet, 'Cells', args);
ole2.destroy_arglist(args);



ole2.invoke(application,'Quit');

-- Release the OLE2 object handles
ole2.release_obj(application);
ole2.release_obj(workbooks);
ole2.release_obj(workbook);
ole2.release_obj(worksheets);
ole2.release_obj(cell);

END;

Jhoann

 

[sfvb]

DECLARE
-- Declare handles to OLE objects
application ole2.obj_type;
workbooks ole2.obj_type;
workbook ole2.obj_type;
worksheet ole2.obj_type;
worksheets ole2.obj_type;
cell ole2.obj_type;

-- Declare handles to OLE argument lists
args ole2.list_type;

cell_num_value number;
BEGIN
application:= ole2.create_obj('Excel.Application');
Workbooks := OLE2.GET_OBJ_PROPERTY(application, 'Workbooks');

-- Open the required workbook
args:= ole2.create_arglist;
ole2.add_arg(args, 'C:\TEST\EXAMPLE.XLS');
workbook := ole2.invoke_obj(workbooks, 'Open', args);
ole2.destroy_arglist(args);

-- Open the 1st worksheet of that Workbook
args:= ole2.create_arglist;
ole2.add_arg(args, 'Sheet1');
worksheet := OLE2.GET_OBJ_PROPERTY(workbook, 'Sheets', args);
ole2.destroy_arglist(args);

-- Get value of cell A1 of the 1st worksheet
args:= ole2.create_arglist;
ole2.add_arg(args, 1);
ole2.add_arg(args, 1);
cell:= ole2.GET_OBJ_PROPERTY(worksheet, 'Cells', args);
ole2.destroy_arglist(args);
cell_num_value := OLE2.GET_NUM_PROPERTY(cell, 'Value');
ole2.release_obj(cell);

message('cell A1 on sheet 1 = '||cell_num_value);

ole2.invoke(application, 'Quit');

-- Release the OLE2 object handles
ole2.release_obj(worksheet);
ole2.release_obj(workbook);
ole2.release_obj(workbooks);
ole2.release_obj(application);
END;


You had most of it right. The text in red is code I changed. The text in blue is code I added. Also you could delete the variable "worksheets" since it isn't used.

This should work if cell A1 contains a number. If cell A1 contains text then you would want to use something like this:
cell_char_value := OLE2.GET_CHAR_PROPERTY(cell, 'Value');
instead of:
cell_num_value := OLE2.GET_NUM_PROPERTY(cell, 'Value');

Also, when you release an object, you want to do it in the reverse order that they were created.

 

[jhoann]

Thanks a lot. God Bless

 

[rawl]

Been following these threads hoping for help with a similar problem.

What about inserting values in an embedded excel spreadsheet? Once one gets a handle to the OLE2 automation object, the GET_OBJECT_PROPERTY call for handles to the Workbooks, or other lower properties all fail.

Starting with:

obj_hnd := get_interface_pointer('graph01');
activate_server('graph01');

with graph01 being the ole2 container name, a handle value seems to be returned but using it in a statement like:

workbooks := OLE2.GET_OBJ_PROPERTY obj_hnd, 'workbooks');

just fails.

Docs state that obj_hnd should be a valid ole2.ole_type handle to the spreadsheet embedded in the chart01 ole container but behavior indicates perhaps otherwise?

Glad for any help.

 

[hqlx]

Do we have another way to do this. I heard OLE cannot work in Oracle 9i AS.

Thanks