Hi Daniel,

I think you should add these tests + Tom's tests in the FAQ.

Thanks again.

>FWIW, check out FAQ #8026.
>During testing and benchmarking I found some interesting results.
>
>The COPY TO command in VFP works very fast compared to
>anything that involves automation. However, since Copy2Xls()
>uses automation only to open the exported file and to save it
>under a different format, there is almost no performance
>penalty.
>In fact, benchmarks on copying 16,383 records on two machines
>showed some interesting results:
>
>IBM T21 800MHz 256MB RAM:
>

>------------- -------------    -----------
>copy2Xls()    COPY TYPE XL5    Differenece
>------------- -------------    -----------
>3.58 s        5.34             - 33 %      !!!
>

>Dell Dimension 667MHz 128MB RAM:
>

>------------- -------------    -----------
>copy2Xls()    COPY TYPE XL5    Differenece
>------------- -------------    -----------
>3.28 s        1.48             + 121 %
>

>
>While the runtime for the Copy2Xls() program was almost
>identical on both machines, the native COPY TO XL5 command
>run almost 4 times slower on the IBM than on the Dell. I have
>no idea what could cause this big difference.
>
>I have just benchmarked exporting a 65,000 row table with 9 columns:
>
>IBM T21 800MHz 256MB RAM:
>

>------------- -------------    -----------
>copy2Xls()    COPY TYPE XL5    Differenece
>------------- -------------    -----------
>11.65 s        5.21            + 45 %
>

>The native COPY TO command runs twice as fast but
>only copies a fourth of the data. In conclusion, the copy2Xls() runs
>*much* faster than anything that involves automation and at least under
>certain circumstances even faster than the native command.
>
>Any ideas why the native command on my IBM T21 is so slow?
>
>
>>Cindy, here is what I found out via some testing of my own. I created
>>a 6 column cursor containing 65,000 rows of data (near the Excel 64K limit).
>>
>>I then wanted to see how long it would take for the various copy methods
>>to work. Here are the results:
>>
>>1.) COPY TO ... TYPE XLS - 10.813 seconds but only 16,383 rows copied.
>> I did this to show that VFP does not report an error when reaching
>> the 16,383 limit.
>>
>>
>>2.) Application.DataToClip(,,3) - 54.422 seconds.
>>
>>3.) COPY TO filename DELIMITED WITH TAB then
>> _CLIPTEXT = FILETOSTR(filename) - 37.172 seconds.
>>
>>Options 1 and 2 take about the same time with less than 20K rows.
>>
>>It is interesting that option 3 with two VFP commands is indeed faster than the single DataToClip()...
>>
>>
>>
>>>Wow - interesting to know. I guess I'll have to do some time trials!
>>>
>>>I do a lot of ad hoc reportig and use DataToClip() as a one-time thing, but if you're programming it makes sense to do it the most efficient way.
>>>
>>>To others reading, you may want to add

>>>IF FILE([FileName])
>>>    ERASE FileName
>>>ENDIF

>>>
>>>
>>>
>>>>Believe it or not, but the following is actually faster than DataToClip()
>>>>especially for a large number of rows:
>>>>
>>>>
>>>>COPY TO filename DELIMITED WITH TAB
>>>>_CLIPTEXT = FILETOSTR(filename)
>>>>oExcel.ActiveSheet.Paste()
>>>>_CLIPTEXT = “” && clear out the clipboard
>>>>
>>>>
>>>>>Automation will do it, but I think the limit then is 64K. After that you will need additional sheets or workbooks.
>>>>>
>>>>>A fast way is to select an amount of data and then use

>>>>>_VFP.DataToClip( , , 3)
>>>>>oExcel.Paste()

>>>>>
>>>>>This makes an array, and you are limited by the max size of an array so if you have a lot of columns you will need to paste fewer rows at a time.
>>>>>
>>>>>Another method is to COPY TO with a small piece at a time and Workbooks.Add() each piece.
>>>>>
>>>>>A third way is to copy to a DBF and automate opening that and saving as an XLS.
>>>>>
>>>>>>HI,
>>>>>>I have queries that may contain 100-500k of rows. I want to export them in Excel. I tried COPY TO...TYPE XL5. It only copy 16383 rows. I know that Excel is able to hold more than that. Is there another solution for it ? Ole automation surely ?
>>>>>>
>>>>>>Thank you,
>>>>>>Christian Cote