>>>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.
>
>Actually I only forgot to Set MemoWidth properly... because at the default 50 it will probably not find the first line+chr(13) in the _cliptext at all. The rest should be OK.
>
>>>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?
>
>That's the old sarcastic joke about a typical dude from my area, who did it that way "to hurt the cat less". But if its speed is dropping with the square of the record count, it really hurt less.
>
>PasteSpecial() is about 20 times faster than filling cell by cell, at the sizes that I had (between a dozen and a couple of thousands of records), by measurements I made then. You may find out that filling one cell at a time, with your load, may take more than just a single forever.
>
>So... select A1, paste 1000 records (don't remember which parameter in .datatoclip is the record count, intellisense will know that), select A1001, paste another thousand... should be fast enough, or your machines are still using vacuum tubes.
Thanks
As I've already got the code to create each of the sheets, I was thinking of giving Naomi's func. a try at the moment.
- 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.