>>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()
Hmmmm, I did originally but I got a dispatch error. As you said "off the top of my head ..." I assumed you'd chosen the wrong object. Ayway, as you say, it takes too long, esp. with the saize of files I'm dealing with.
>
>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?
Sounds like prolonging the agony for the cat. Or do you mean a cat o' nine tails, which inflicts pain on humans?
>
>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.
- Whoever said that women are the weaker sex never tried to wrest the bedclothes off one in the middle of the night
- Worry is the interest you pay, in advance, for a loan that you may never need to take out.