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