Hello OI Commnity:

I have a routine which takes data from the Temp table and exports it to Excel. The program works fine with up to 400 rows of information. However, with 3000+ rows it fails. My logic may be flawed, or the method, or Heaven forbid, OI. Basically I write data into to an editbox then export it a cell at a time. Can someone point out the error of my code or suggest a better method? Here's the code:

For r=1 to nrows

Read row from tempvar, r Then

	For c=1 to CntFlds 

		x=Set_Property('BUYEXPORT.DDE','TEXT', row)

		string=R' :r: 'C' : c

		status=Set_Property('BUYEXPORT.DDE', 'DDEITEM', string)

		status=Set_Property('BUYEXPORT.DDE', 'DDELINK', 'AUTO')

		status=Set_Property('BUYEXPORT.DDE', 'DDEDATA', row)

		If status Else

		  Msg(@window, MsgUp)

			error=Export failed.  Make sure Excel is open' : crlf$

			error := 'and the file ' : excel: ' is displayed.'

	  	status=Set_Property('BUYEXPORT.DDE', 'DDELINK', 'OFF')

		  Return

		End  	

	next c

End Else

	error=Error reading TEMP'

  Msg(@window, MsgUp)

  Return

End

Yield()

 While Msg(@window, MsgUp, r, MSGINSTUPDATE$)

 Yield()

next r

Blair,

Your code looks fine to these untrained eyes. But perhaps each request reduces available resources? Can you try to export in larger chunks? Isn't it possible to set a whole range and not just a cell (DDEITEM)? You could e.g take one whole row at a time.

- Oystein -

Blair,

Does it make any difference if you let Excel do a save now and then? Saving - see http://www.revelation.com/THEWORKS/OIdiscuss.nsf/ef8405490de3608f85256700005245e4/40A2B58B6CFC8291852568230078C617?OpenDocument.

- Oystein -

Blair,

I think Oystein is right, the reason why the utility breaks down is most probably because of a memory leak taking place as you are exporting to one cell at a time.

Another thing is that you may want to close the connection each time a part of data is transfered to EXCEL, I would recommend adding the

syntax…..'DDELINK', OFF, much earlier in the code. I have added it below, where I think it should work:

For r=1 to nrows

Read row from tempvar, r Then

For c=1 to CntFlds

x=Set_Property('BUYEXPORT.DDE','TEXT', row)

string=R' :r: 'C' : c

status=Set_Property('BUYEXPORT.DDE', 'DDEITEM', string)

status=Set_Property('BUYEXPORT.DDE', 'DDELINK', 'AUTO')

status=Set_Property('BUYEXPORT.DDE', 'DDEDATA', row)

status=Set_Property('BUYEXPORT.DDE', 'DDELINK', 'OFF')

If status Else

Msg(@window, MsgUp)

error=Export failed. Make sure Excel is open' : crlf$

error := 'and the file ' : excel: ' is displayed.'

Thanks for reply Oystein,

I'm have no idea of how to modify the code to export a row at a time. The way I've coded it is CPU intensive and very slow, which can

be a factor when you want to export 800 row with 8 columns.

Do you know how it can be done a row at a time?

Hey Arjun!

I spoke with Mike Ruane. He said to set it to off after 300 rows or so. That didn't work at my installed site. Perhaps closing link after each cell would work, but surely there is a faster way to get this job done.

I'll give it a shot!

Arjun, do you know how the code could be modified to export a row at at time?

Blair,

The docs say you can set the values of a whole range of Excel cells, and by experimenting a little I've found out exactly how. E.g with DDEITEM equal to 'R1C1:R1C3' you can set the values of the first three cells of the first row. The values (DDEDATA) must be delimited with Char(9)=Tab (convert @FM to Char(9) in Row). You can also set the values of a rectangle of cells. E.g with DDEITEM=R1C1:R2C3' you can set the values of cells 1-3 of row 1-2. In the values you must tyhen have CrLf$ between each row.

Btw - are you sure you should set both TEXT and DDEDATA? (Probably not important.)

- Oystein -

Good morning Arjun,

I was able to try the code you suggested this morning. It works

much better and is suprisingly fast.

Thanks!

View this thread on the Works forum...