Home » Developer & Programmer » Forms » Error closing Excel file generated using OLE2
Error closing Excel file generated using OLE2 [message #221121] Sat, 24 February 2007 01:02 Go to next message
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 Go to previous message
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
Previous Topic: About enable or disable a button
Next Topic: date problem
Goto Forum:
  


Current Time: Fri Dec 02 16:22:31 CST 2016

Total time taken to generate the page: 0.24547 seconds