Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

REad an excel cell using oracle forms

710 views
Skip to first unread message

Manikandan

unread,
Mar 17, 2003, 12:47:37 AM3/17/03
to

Hi,
I have the follwoing code to read an excel cell from oracle forms.But
i am getting the error 'ora-305522' during the excution of the line

worksheets := ole2.invoke_OBJ(workbooks, 'Worksheets');

But i tried with
worksheets := ole2.GET_OBJ_PROPERTY(workbooks, 'Worksheets');

but of no use...still getting the same error...any one there to help me
out??? Is there any way to modify the code???

Here is my code:

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;
check_file text_io.file_type;
no_file exception;
PRAGMA EXCEPTION_INIT (no_file, -302000);
cell_value varchar2(2000);

BEGIN

-- Check the file can be found, if not exception no_file will be raised
check_file := TEXT_IO.FOPEN('C:\test.xls','R');
TEXT_IO.FCLOSE(check_file);

application:= ole2.create_obj('Excel.Application');
workbooks := ole2.GET_OBJ_PROPERTY(application, 'Workbooks');
worksheets := ole2.invoke_OBJ(workbooks, 'Worksheets');

-- Open the required workbook
args:= ole2.create_arglist;
ole2.add_arg(args, 'C:\test.xls');
workbook := ole2.GET_OBJ_PROPERTY (workbooks, 'Open', args);
ole2.destroy_arglist(args);

-- Open worksheet Sheet1 of that Workbook
args:= ole2.create_arglist;
ole2.add_arg(args, 'Sheet1');
worksheet := ole2.GET_OBJ_PROPERTY (worksheets, '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.GET_OBJ_PROPERTY (worksheet, 'Cells', args);
ole2.destroy_arglist(args);
cell_value :=ole2.get_char_property(cell, 'Value');
message(cell_value);

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);

EXCEPTION
WHEN no_file THEN
MESSAGE('File not found.');

END;

--
Posted via http://dbforums.com

SoulSurvivor

unread,
Mar 17, 2003, 9:25:27 AM3/17/03
to
You have to open up all the objects in the correct order. Try opening
WorkBook before WorkSheets.
Also, worksheets is a property of Workbook, not Workbooks.

Hierarchy as follows:

APPLICATION
WORKBOOKS
WORKBOOK
WORKSHEETS
WORKSHEET

Try rewriting you code to

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.xls');
workbook := ole2.GET_OBJ_PROPERTY (workbooks, 'Open', args);
ole2.destroy_arglist(args);

worksheets := ole2.invoke_OBJ(workbook, 'Worksheets');
..
..
..

See examples on Metalink for more info.

M

Manikandan <membe...@dbforums.com> wrote in message news:<2650961.1...@dbforums.com>...

Manikandan

unread,
Mar 17, 2003, 11:56:24 PM3/17/03
to

Hi,
Thanks a lot for your valuable reply.I have modified my code
like below.
Moreover is there any possibility to rename,find & replace string the
file using ole2 package.
Here i am reading one cell.Is there any way to read one full
record(first row in excel)?.Please help me if you can...

my code is:

DECLARE

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

cell ole2.obj_type;

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

Check_file text_io.file_type;
no_file exception;
PRAGMA exception_INIT (no_file, -302000);
cell_value varchar2(2000);

beGIN

-- Check the file can be found, if not exception no_file will be raised

Check_file := TEXT_IO.FOPEN('C:\test.XLS','R');
TEXT_IO.FCLOSE(Check_file);

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.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.get_obj_property(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.get_obj_property(worksheet, 'Cells', args);


ole2.destroy_arglist(args);
cell_value :=ole2.get_char_property(cell, 'Value');
message(cell_value);

args:=ole2.create_arglist;
ole2.add_arg(args,'C:\Ora.prn');
ole2.add_arg(args,'Formatted text(Space delimited)(*.prn)|*.prn|');
ole2.invoke(worksheet,'SaveAs',args);
ole2.destroy_arglist(args);
message('Hi am passed');

ole2.invoke(application,'Quit');
message('Hi am still passed');


-- Release the OLE2 object handles

ole2.release_obj(cell);
ole2.release_obj(worksheet);
ole2.release_obj(workbook);
ole2.release_obj(workbooks);
ole2.release_obj(application);
message('Hi am in safer side');

exception
WHEN no_file THEN
MESSAGE('file not found.');
WHEN OTHERS THEN
MESSAGE(sqlerrm);
PAUSE;
FOR i IN 1 .. tool_err.nerrors LOOP
MESSAGE(tool_err.message);
PAUSE;
tool_err.pop;
END LOOP;

END;

Thanks

SoulSurvivor

unread,
Mar 18, 2003, 9:40:17 AM3/18/03
to
You can do search, replace etc in OLE2. Best way is to replicate what
you want to do in Excel, record a macro and look at the VBA code. You
then call the VBA methods via ole2.add_arg add ole2.invoke.

You cannot return a row from Excel into your Forms PL/SQL.

M

Manikandan <membe...@dbforums.com> wrote in message news:<2655236.1...@dbforums.com>...

Manikandan

unread,
Mar 19, 2003, 12:21:08 AM3/19/03
to

Hi,
Thanks again.Can you please tell me where i can look the code for VBA
Methods? in oracle forms.

Thanks

SoulSurvivor

unread,
Mar 20, 2003, 5:59:40 AM3/20/03
to
The VBA methods will be in Excel or MS Word.

In Excel, go to Tools > MAcros > Visual Basic Editor
In the VBA editor, do View > Object Browser.

This should show you the objects and any methods associated with them.
Apart from that, i used to just use the VBA help to get by.

M

Manikandan <membe...@dbforums.com> wrote in message news:<2658981.1...@dbforums.com>...

Manikandan

unread,
Mar 21, 2003, 12:20:24 AM3/21/03
to

Hi,
Thanks again.....But i have been told to automate these process only
by accepting excel file from the user.

Thanks

0 new messages