>>I am using VFP 5.0 and Excel 5.0 to generate Excel reports from SQL Server 6.5 data.
>>
>>The following VFP code creates mutliple spreadsheets in a single workbook. It seems to work just fine. The problem is that the scan is scanning 250,000+ records and it takes several DAYS to comnplete.
>>
>>Am I doing anything in the code to slow this down? Is there a faster way to do this? (There just HAS to be a way to do this faster.)
>>
>>The output will always have more than 64k rows, so it will not fit into a single spreadsheet, so I am creating multiple spreadsheets as needed, limiting each spreadsheet to approximately 30,000 rows.
>>
>>I do not have the option of creating multiple WORKBOOKS because the final WORKBOOK is being e-mailed to the user and the user/customer wants to be able to easily navigate this report. Multiple WORKBOOKS would only confuse them and would also cause me to fall short of what they were promised.
>>
>>So-o-o-o-o, I would appreciate any help I can get from good old Universal Thread.
>>
>>Bob
>>
>>
>>**************************************************************
>>l_nRow = 5
>>SELECT &lzipname
>>wait window 'Now process ' + lzipname
>>SCAN
>> o.worksheets("sheet"+allt(str(i))).cells[l_nRow,1].VALUE = &lzipname..lt_desc
>>
>> o.worksheets("sheet"+allt(str(i))).cells[l_nRow,2].VALUE = &lzipname..li_name
>>
>> o.worksheets("sheet"+allt(str(i))).cells[l_nRow,3].VALUE = ALLTRIM(&lzipname..na_zip)
>>
>> o.worksheets("sheet"+allt(str(i))).cells[l_nRow,4].VALUE = ALLTRIM(&lzipname..country)
>>
>> o.worksheets("sheet"+allt(str(i))).cells[l_nRow,5].VALUE = &lzipname..gn_male
>>
>> o.worksheets("sheet"+allt(str(i))).cells[l_nRow,6].VALUE = &lzipname..gn_female
>>
>> o.worksheets("sheet"+allt(str(i))).cells[l_nRow,7].VALUE = &lzipname..gn_unkn
>>
>> o.worksheets("sheet"+allt(str(i))).cells[l_nRow,8].VALUE = &lzipname..na_home
>>
>> o.worksheets("sheet"+allt(str(i))).cells[l_nRow,9].VALUE = &lzipname..na_busines
>> l_nRow = l_nRow + 1
>>ENDSCAN
>>**************************************************************
>Hi,
>I think you're at least partially serious when saying "DAYS". I didn't dare to test with scan...endscan but I think would really get more than half hour to complete. If it's the case this routine could cut down it to about 1-1.5 mins per 30000 rows page. Actual time would vary of course depending hardware and recsize. MS Query and ODBC are really slow. If it's acceptable that about 300000 recs are transferred to Excel in 12-13 mins then try this else just ignore. Idea is to paste bulk amounts to excel sheet instead of change value per cell. application.datatoclip has a limit to work correctly but I don't know the limit. Just learn by trial (ie: home()+"samples\data\customer" structure permits 2000 copies w/o any error but 3000 copies GPFs). Changing this number (lnPerPaste) by 1000 changes the per page insertion time by less than 6 secs on my system. Hope it helps.
LPARAMETERS tcTable
>USE (tcTable)
>#DEFINE lnPerSheet 30000
>#DEFINE lnPerPaste 2000
>
>start = seconds()
>
>lnTotal = reccount()
>lnNeededSheets = ceiling( lnTotal / lnPerSheet )
>WAIT window nowait "Pls wait sending data to Excel..."
>? "Total Pages : "+padr(lnNeededSheets,3," ")
>oExcel = createobject("Excel.application")
>WITH oExcel
> .workbooks.add
> WITH .activeworkbook
> lnCurrentSheetCount = .sheets.count
> .sheets.add(,.sheets(lnCurrentSheetCount),;
> lnNeededSheets - lnCurrentSheetCount)
> FOR ix = 1 to .sheets.count
> WITH .sheets.item(ix)
> .name = "Page "+padl(ix,3,"0")
> .activate
> =InsertGroup(ix)
> ENDWITH
> ENDFOR
> .sheets.item(1).activate
> ENDWITH
> .visible = .t.
>ENDWITH
>? "Total Elapsed : ",seconds() - start
>
>FUNCTION InsertGroup
>LPARAMETERS tnSheetNumber
>LOCAL jx
>lnPasteStart = ( tnSheetNumber - 1 ) * lnPerSheet
>lnWillPaste = ceiling(lnPerSheet / lnPerPaste)
>FOR jx = 1 to lnWillPaste
> lnPasteGroupStart = lnPerPaste*(jx-1)+1
> IF lnPasteStart + lnPasteGroupStart > reccount()
> EXIT
> ENDIF
> GO lnPasteStart + lnPasteGroupStart
> Application.datatoclip(alias(), lnPerPaste, 3)
> WITH oExcel.activesheet
> .cells(lnPasteGroupStart,1).select
> .Paste
> IF jx > 1
> .Rows(lnPasteGroupStart).delete
> ENDIF
> ENDWITH
>ENDFOR
>? "Page no : "+;
> padl(tnSheetNumber,3," ")+;
> "/"+;
> padr(lnNeededSheets,3," ")+ ;
> " Elapsed : ",seconds() - start
Cetin
I cannot get this line of code to work.
> Application.datatoclip(alias(), lnPerPaste, 3) && RecSet is with headers
I can find no reference to "application.datatoclip()" in the VBA help. Can you enlighten me as to how this is used? When I run this it inserts blank rows with headers.
Thanks for you help.
Bob
Database Development Team