Jaime Vasquez wrote:
> Ricardo Aráoz wrote:
>> Jaime Vasquez wrote:
>>
>>> Ricardo Aráoz wrote:
>>>
>>>> My reports generate Excel files, if the report is too long (more than
>>>> 64000 lines) I use different pages in the same file. I have two ways to
>>>> do it, either I export up to say 15000 lines with a copy to command to a
>>>> temporary excel file, then open it (as an excel object), copy it's
>>>> contents to the clipboard (through excel command) and then copy the
>>>> clipboard to a new page in my multi page excel file. The pro of this
>>>> method is speed, the con is that if the customer uses the clipboard he
>>>> interferes with the report.
>>>> The other way is to insert the data cell by cell, but if the file is too
>>>> large it gets slow.
>>>> Does any of you know of some other way to optimize this?
>>>
>>> Ricardo,
>>>
>>> The easiest and fastews way, imo, is to read the data with ado, next use
>>> the copyfromrecordset method to copy to excel.
>>>
>>> It will be fast and you don't have to deal with the volatile clipboard.
>>>
>>> *Create ado connection and recordset object
>>> loConn = NewObject('ADODB.Connection')
>>> loRs = NewObject('adodb.recordset')
>>> loconn.ConnectionString = "Provider=vfpoledb.1;Data
>>> Source=C:\myvfpdbfolder\;Collating Sequence=general;"
>>> loconn.Open
>>> lors.Open("select * from credidt.dbf", LoConn)
>>>
>>> *excel object:
>>> loExcel = NewObject("Excel.application")
>>> loWorkBook = loexcel.Workbooks.Add
>>> loWorkSheet = loexcel.ActiveWorkbook.Worksheets.Add
>>> loExcel.visible = .T.
>>> loworksheet.range("A1").CopyFromRecordset(lors) && copy data to excel
>>>
>>>
>>
>> Te agradezco mucho Jaime. The problem is I process my data in the local
>> app, I take data from two different engines and combine and process it
>> so I am actually dealing with a VFP cursor which I have to insert into a
>> workbook in chunks of 15 to 64000 records per worksheet. I can see that
>> if I do something like "loworksheet.ActivePage.CopyFromRecordSet(lors)"
>> I might get the file into a given page, but I would still have two
>> problems, first how to divide the file in chunks (doable), and second I
>> have my data in a cursor. Is there a way of converting a local VFP
>> cursor into a record set?
>>
>> Thanks again
>>
>
>
> Yes, a VFP cursor can be converted to an ADO recordset, see:
> http://support.microsoft.com/kb/192762
>
>
> If you want to copy just a number of records, use the sencond parameter
> of the CopyFromRecordSet method:
>
> *get a reference to the range where you want the results:
> oWorkSheet = oWorkBook.Worksheets("Page1")
> oworksheet.Range("a1").CopyFromRecordset(loRs, 150) && copy first 150
> records
>
> * next page
> oWorkSheet = oWorkBook.Worksheets("Page2")
> oworksheet.Range("a1").CopyFromRecordset(loRs, 150) && copy next 150
> records.
>
> And so on...
>
>
Thanks a lot Jaime. This will be really useful.
>
>
> HTH
>
>
>
> Saludos,
> Jaime Vasquez
> Guatemala C.A.
>
>
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
