| Error closing Excel file generated using OLE2 [message #221121] |
Sat, 24 February 2007 01:02  |
boyet-x
Messages: 23
Registered: August 2006
|
Junior Member |
|
|
Hi all,
Is there anyone here familiar with Ole2?
I am generating a report to an excel file using Ole2 package.
Due to excel row size constraint (only ~65k rows allowed),
I implemented it to add new worksheets every time the record size per sheet exceeds 65k.
The process is done and is saved successfully. But when I try to close the MSExcel App, an error pops up (sorry it's written in Chinese, but it says something like "cannot read memory.. [memory address].." then ask if to debug or not). This is not critical bug but its annoying. And I think theres a better way to do this.
I have released all objects. I even tried to release each worksheet object every time I added new one.
What could have caused it.
Really needed your help..
Heres my procedure to add new worksheet.
=========================================
PROCEDURE PP_ADD_SHEET(p_name in varchar2) IS
n_sheet_idx number;
n_sheet_cnt number;
args OLE2.LIST_TYPE;
BEGIN
n_sheet_idx := OLE2.GET_NUM_PROPERTY(Ole_Excel.worksheet, 'Index');
n_sheet_cnt := OLE2.GET_NUM_PROPERTY(Ole_Excel.worksheets, 'Count');
n_sheet_idx := n_sheet_idx + 1;
if n_sheet_idx = n_sheet_cnt + 1 then
n_sheet_idx := 1;
end if;
-- Go to next sheet
args := OLE2.Create_Arglist;
OLE2.add_arg(args, n_sheet_idx);
Ole_Excel.worksheet := OLE2.GET_OBJ_PROPERTY(Ole_Excel.worksheets,'Item', args);
OLE2.Invoke(Ole_Excel.worksheet,'Select');
OLE2.Destroy_Arglist(args);
-- Add new sheet
OLE2.Release_Obj(Ole_Excel.worksheet);
Ole_Excel.worksheet := OLE2.Invoke_Obj(Ole_Excel.worksheets, 'Add');
Ole2.Set_property(Ole_Excel.worksheet, 'Name', p_name);
OLE2.Release_Obj(Ole_Excel.worksheet);
Ole_Excel.worksheet := Ole2.Get_Obj_Property(Ole_Excel.application, 'ActiveSheet');
END;
================================================
P.S. The error does not happen if there's only 1 newly generated sheet. So I guess the error is in this procedure.. or in the usage of this procedure ? Also when I programatically close
the file (using 'Quit'), it works fine. But it's required NOT to close the file after generation.
Kindly help!
-- boyet
|
|
|
|
| Re: Error closing Excel file generated using OLE2 [message #224143 is a reply to message #221121] |
Tue, 13 March 2007 04:18  |
boyet-x
Messages: 23
Registered: August 2006
|
Junior Member |
|
|
Hi all,
Finally, I was able to solve this problem.
I want to share this to all people who were stuck with the same problem as me.
I modified my previous function.
================================
PROCEDURE PP_ADD_SHEET(p_name in varchar2 default null) IS
n_sheet_idx number;
n_sheet_cnt number;
args OLE2.LIST_TYPE;
ws OLE2.OBJ_TYPE;
BEGIN
n_sheet_idx := OLE2.GET_NUM_PROPERTY(Ole_Excel.worksheet, 'Index');
n_sheet_cnt := OLE2.GET_NUM_PROPERTY(Ole_Excel.worksheets, 'Count');
n_sheet_idx := n_sheet_idx + 1;
if n_sheet_idx = n_sheet_cnt + 1 then
n_sheet_idx := 1;
end if;
-- Go to next sheet
args := OLE2.Create_Arglist;
OLE2.add_arg(args, n_sheet_idx);
ws := OLE2.GET_OBJ_PROPERTY(Ole_Excel.worksheets,'Item', args);
OLE2.Invoke(ws,'Select');
OLE2.Destroy_Arglist(args);
OLE2.Release_Obj(ws);
-- Add new sheet
OLE2.Release_Obj(Ole_Excel.worksheet);
Ole_Excel.worksheet := OLE2.Invoke_Obj(Ole_Excel.worksheets, 'Add');
if p_name is not null then
Ole2.Set_property(Ole_Excel.worksheet, 'Name', p_name);
end if;
END;
================================
I'm not quite sure how it works but i guess it has something to do with assigning (and re-assigning object variables.)
When invoking to add a new worksheet, it always inserts to the left of the current (selected) sheet. So I had to select the sheet after the newest (the default Sheet 1) so that sequence of the added sheets will be from left to right.
However, the error happens when I assign Ole_Excel.worksheet to it.
(Btw, Ole_Excel is my own defined package in a library.)
I tried to assign a new worksheet variable instead ws to assign to this temporary selection and it works fine.
-- boyet-x
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
