DDL and Excel continued (None Specified) [Revelation On-Line Wiki]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
{{tag>category:"None Specified" author:"Blair Massey" author:"Oystein Reigem" author:"Arjun Revelation"}}

[[https://www.revelation.com/the-works|Join The Works program to have access to the most current content, and to be able to ask questions and get answers from Revelation staff and the Revelation community]]


==== DDL and Excel continued (None Specified) ====
=== At 09 NOV 1999 01:33:55PM Blair Massey wrote: ===
 
 
<QUOTE>
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
</QUOTE>
----

=== At 11 NOV 1999 04:45AM Oystein Reigem wrote:  ===

<QUOTE>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 -

</QUOTE>

----

=== At 11 NOV 1999 05:10AM Oystein Reigem wrote:  ===

<QUOTE>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 -

</QUOTE>

----

=== At 11 NOV 1999 09:27AM Arjun Revelation wrote:  ===

<QUOTE>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.'







</QUOTE>

----

=== At 11 NOV 1999 08:45PM Blair Massey wrote:  ===

<QUOTE>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?

</QUOTE>

----

=== At 11 NOV 1999 08:47PM Blair Massey wrote:  ===

<QUOTE>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.



</QUOTE>

----

=== At 11 NOV 1999 08:48PM Blair Massey wrote:  ===

<QUOTE>I'll give it a shot!

</QUOTE>

----

=== At 11 NOV 1999 08:53PM Blair Massey wrote:  ===

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

</QUOTE>

----

=== At 12 NOV 1999 04:10AM Oystein Reigem wrote:  ===

<QUOTE>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 -



</QUOTE>

----

=== At 12 NOV 1999 09:48AM Blair Massey wrote:  ===

<QUOTE>Good morning Arjun,



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

much better and is suprisingly fast.



Thanks!

</QUOTE>


[[https://www.revelation.com/revweb/oecgi4p.php/O4W_HANDOFF?DESTN=O4W_RUN_FORM&INQID=WORKS_READ&SUMMARY=1&KEY=8AC0B97ED05785D8852568240065FB8F|View this thread on the Works forum...]]