>I tried you code, as below. It seemd a good idea BUT the data I'm dealing with is in humungous tables sometimes, like the etmdata table below. Several issues arose:
>1) it took forever to fill _cliptext (as I imagined it would, given the size of the table)
>2) I got some data in the w/s after the first ox.selection.pastespecial() (some 16K by the looks of it) but
>os2.range("a1").select forced an OLE exception liek "Cannot access the range" (shoulda noted down, eh?)
>3) the workbook failed to close (still left up as a procss in the TM, where I had to end it) and, when I looked in the w/w there was ziltch.
>
>Nice idea but I don't know what to do about the problems. Looks like I'll need to do it in a loop, as Jim's suggestion.
>
>Terry
>
>
>LOCAL ox as Excel.Application
>ox = CREATEOBJECT('Excel.Application')
>ob = ox.Workbooks.Open('P:\FBVFP\book1.xls')
>os1 = ob.Worksheets(1)
>os2 = ob.Worksheets(2)
>os3 = ob.Worksheets(3)
>
>USE p:\fbvfp\officevfp\essexdata\etmdata.dbf In 0
>select etmdata
>_vfp.datatoclip(,,3)
>_cliptext=strtr(_cliptext, mline(_cliptext,1)+chr(13), "")
>os1.range("a1").select
>ox.selection.pastespecial()
>os2.range("a1").select
>ox.selection.pastespecial()
>ox.ActiveWorkBook.Close()
>ox = NULL
>RELEASE ox
>
More or less what I was using, and I had reports with a few thousand rows. The only difference was that I did
os1.selection.pastespecial()
not the oX (oExcel, I presume). Maybe you could do this in a loop, with, say, a thousand records at a time? If the speed drops squarely with the number of records (as I presume it does), then the cat would hurt less if you chopped its tail quarter of an inch at a time?
Another observation: this operation devours memory. I've seen a batch of reports done in this matter take between 20 minutes and whichever portion of eternity suffices to get the user PO'd enough to kill it, and less than three minutes on another machine which didn't have 30 windows open. The total size of xls files produced was cca 20M per batch.