Re: REad an excel cell using oracle forms

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 <member12054_at_dbforums.com> wrote in message news:<2655236.1047963384_at_dbforums.com>...
> 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
Received on Tue Mar 18 2003 - 15:40:17 CET