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...
>
>
>
>
> HTH
>
>
>
> Saludos,
> Jaime Vasquez
> Guatemala C.A.
>
Jaime, it works! And it solves my problem.
Only glitch is that the second 'CopyFromRecordset(loRs, 150) && copy
next 150' does not copy the next 150 but copies the first 150 records
again. Not a problem, I can copy my chunks of records to a cursor and
then do a cursor at a time (rs.Delete(150) does not work either).
Anyway if you can see a more elegant solution I'll appreciate it.
Thanks for your help.
Here is my test code :
---------------------------------------------------
loWorkBook = loExcel.WorkBooks.Add
loWorkBook.ActiveSheet.name = 'PrimeraHoja'
loWorkBook.Worksheets("PrimeraHoja").Range('a2').CopyFromRecordSet(lors,20)
loWorkBook.WorkSheets.Add(, loWorkBook.ActiveSheet)
loWorkBook.ActiveSheet.Name = 'SegundaHoja'
loWorkBook.WorkSheets("SegundaHoja").Range('a2').CopyFromRecordSet(lors,20)
loWorkBook.Application.Visible = .T.
----------------------------------------------------
and the same first 20 records are copied in both pages.
So I do :
-----------------------------------------------------
go top in MyCursor
lors = dbf2rs('MyCursor', 'Next 20')
loWorkBook = loExcel.WorkBooks.Add
loWorkBook.ActiveSheet.name = 'PrimeraHoja'
loWorkBook.Worksheets("PrimeraHoja").Range('a2').CopyFromRecordSet(lors,20)
go 21 in MyCursor
lors = dbf2rs('MyCursor', 'Next 20')
loWorkBook.WorkSheets.Add(, loWorkBook.ActiveSheet)
loWorkBook.ActiveSheet.Name = 'SegundaHoja'
loWorkBook.WorkSheets("SegundaHoja").Range('a2').CopyFromRecordSet(lors,20)
loWorkBook.Application.Visible = .T.
-----------------------------------------------------
_______________________________________________
Post Messages to: ProFox@leafe.com
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.
