PB 10.5 OLE Excel crashes after about 105068 Excel cells assigned

[rainernagel]

WHAT does a DW_control.SaveAs(ls_PathwithFileName, Excel8!,
TRUE)
do better than what I do?
(well, DW_control crashes after 65536 rows, instead of
adding sheets )

I have PB 10.5.1 and MS Office XP 2002 (V.10) with SP3 and
Office 2007 compatibility patch installed
also "excel.application.8" does work on my machine, but not
V.9

after retrieve my DWO has about 116000 rows (and 69
columns), that´s why I
must split it manually into multiple XL worksheets

Powerscript:
wf_createExcelFile( readonly? super_dw adw_dw1)

long ll_xlrow, ll_r, ll_remainingrows
OLEObject lole_excel, lole_xlsub

li_status =
lole_excel.ConnectToNewObject("excel.application.10")
...
lole_excel.workbooks.Add //creates "Book1" with 5
(default) worksheets
...

adw_dw1.SetFilter("customer=921")
adw_dw1.Filter()
adw_dw1.Sort()

// Loop through the DWO and Excel 2002 sheet
//dereference
lole_xlsub = lole_excel.workbooks.item[1].worksheets.item
[ 1 ]

...
ll_remainingrows = adw_dw1.RowCount()

For ll_xlrow = 3 to min (ll_remainingrows, 65534) +2
lole_xlsub.cells[ll_r,2] =
adw_dw1.object.data[ll_r, 2]
lole_xlsub.cells[ll_r, 15] =
adw_dw1.object.data[ll_r, 15]
lole_xlsub.cells[ll_r, 19] =
adw_dw1.object.data[ll_r, 19]
lole_xlsub.cells[ll_r, 20] =
adw_dw1.object.data[ll_r, 20]
ll_r = ll_r +1
Next
ll_remainingrows = ll_remainingrows - (ll_r - 1)

PROBLEM
my code crashes after about 105068 Excel cells assigned
(i.e. I am still on
the first XL sheet)
could loop down to row ll_xlrow 26267
cannot remember the exception desc right now.
DO i NEED TO ALLOCATE MORE MEMORY ?

best regards, rainer

[Reynaldo Laqui]

Hi Rainer,

I have the same issue as yours ... do you have solution yet?

Thanks,
Rey

--
Free News Reader
http://put.hk
http://put.hk/reader/forums.sybase.com/sybase.public.powerbuilder.ole-ocx-activex.html

[Wheeley]

On 24 Apr 2009 16:52:06 -0700, Rainer Nagel wrote:

Memory could be your issue but not like you think. Powerbuilder's OLE
implementation leaks memory like a sieve. Basically every OLE call you
make creates another "dispatch" object behind the scene which
performes the Excel call. These dispatch objects are NOT reused and
are left in memory until garbage collection happens. In your case
below, garbage collection does not happen in a timely manner or you.
So you need two force garbage collection. I would add it just before
the start of the for loop and again inside the loop just before the
next statement. This way you do not build up to many dispatch objects
in memory.

Another thing to consider, ALWAYS destroy all oleobjects which connect
to Excel prior to closing Excel. This will ensure all references in
Excel get closed and memory doesn't leak nere as well.

Lastly, you may want to consider upgrading to 11.5.1 which now has
native support for exporting to Excel 12 (Excel 2007).

Wheeley