| how do i display all excel worksheets name in form [message #269440] |
Sat, 22 September 2007 05:41  |
anisam10674
Messages: 18
Registered: March 2007
|
Junior Member |
|
|
|
I have an excel file containing say 3 worksheets in it. What I would like to do now is display names of all 3 worksheets in a datablock multiple text items and allow the user to select any one of those sheets and then import data from the selected excel worksheet onto form's another datablock. any suggestions on how this can be achieved? Thanks in advance.
|
|
|
|
|
|
|
|
|
|
| Re: how do i display all excel worksheets name in form [message #269694 is a reply to message #269662] |
Mon, 24 September 2007 02:24  |
anisam10674
Messages: 18
Registered: March 2007
|
Junior Member |
|
|
|
I had used ole2 to write data onto a new worksheet. What I would like to do is open a user specified worksheet from all the worksheets present in an existing excel file. For this, first thing is to get the names of all the worksheets in that file onto a List-Item in a form. The user will select a worksheet from this list-item and then data can be inserted onto that worksheet through ole2. How can this be done?
|
|
|
|
| Re: how do i display all excel worksheets name in form [message #270458 is a reply to message #269694] |
Thu, 27 September 2007 00:27 |
anisam10674
Messages: 18
Registered: March 2007
|
Junior Member |
|
|
Thank you friends,
I could finally display all worksheets name of an excel file in a list item of a form, courtesy-some useful posts in this forum.
-----
-- Procedure to get the worksheets name of an excel file and display the names in a list item:
-- v_filename is the excel file name specified by user
-- v_blkitemname is the list item name which will display the names of the excel worksheets present in v_filename
procedure get_xl_ws_names(v_filename in varchar2, v_blkitemname in varchar2) is
--declare handles to OLE object
application ole2.obj_type;
workbooks ole2.obj_type;
workbook ole2.obj_type;
worksheets ole2.obj_type;
worksheet ole2.obj_type;
--declare handles to OLE argument lists
args ole2.list_type;
check_file text_io.file_type;
file_not_found exception;
pragma exception_init(file_not_found,-302000);
--user-defined varibles
v_ws_cnt integer;
v_ws_nam varchar2(100);
v_le_cnt integer;
begin
--delete existing elements from list item
v_le_cnt := get_list_element_count(v_blkitemname);
if v_le_cnt>=1 then
for i in 1..v_le_cnt loop
delete_list_element(v_blkitemname,1);
end loop;
end if;
--check whether file exists, else raise file_not_found exception
check_file := text_io.fopen(v_filename,'R');
text_io.fclose(check_file);
--open excel application
application := ole2.create_obj('excel.application');
ole2.set_property(application,'Visible','False');
workbooks := ole2.get_obj_property(application,'Workbooks');
args := ole2.create_arglist;
ole2.add_arg(args,v_filename);
workbook := ole2.get_obj_property(workbooks,'Open',args);
ole2.destroy_arglist(args);
worksheets := ole2.get_obj_property(workbook,'Worksheets');
--get worksheet count into variable v_ws_cnt
v_ws_cnt := ole2.get_num_property(worksheets,'Count');
--get worksheets name and add it into list element of list item
for i in 1..v_ws_cnt loop
args := ole2.create_arglist;
ole2.add_arg(args,i);
worksheet:=ole2.get_obj_property(worksheets,'Item',args);
v_ws_nam := ole2.get_char_property(worksheet,'Name');
ole2.destroy_arglist(args);
ole2.release_obj(worksheet);
add_list_element(v_blkitemname,1,v_ws_nam, v_ws_nam);
end loop;
--close excel worksheet/workbook/application
ole2.invoke(worksheet,'Close');
ole2.invoke(workbook,'Close');
ole2.invoke(application,'Quit');
--release OLE handles
ole2.release_obj(worksheet);
ole2.release_obj(worksheets);
ole2.release_obj(workbook);
ole2.release_obj(workbooks);
ole2.release_obj(application);
exception
when file_not_found then
message('File not found');
end;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
