>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 && Add a new workbook
  WITH .activeworkbook
    lnCurrentSheetCount = .sheets.count
    .sheets.add(,.sheets(lnCurrentSheetCount),;
     lnNeededSheets - lnCurrentSheetCount) && Add new sheets after old ones
    FOR ix = 1 to .sheets.count
      WITH .sheets.item(ix)
        .name = "Page "+padl(ix,3,"0") && Sheet name -> Page00#
        .activate
        =InsertGroup(ix)               && Get data for this page
      ENDWITH
    ENDFOR
    .sheets.item(1).activate           && Activate first sheet
  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 && Done all
  ENDIF
  GO lnPasteStart + lnPasteGroupStart
  Application.datatoclip(alias(), lnPerPaste, 3) && RecSet is with headers
  WITH oExcel.activesheet
    .cells(lnPasteGroupStart,1).select         && Go start of paste cell
    .Paste
    IF jx > 1                                  && Kill header if ix > 1
      .Rows(lnPasteGroupStart).delete
    ENDIF
  ENDWITH
ENDFOR
? "Page no : "+;
  padl(tnSheetNumber,3," ")+;
  "/"+;
  padr(lnNeededSheets,3," ")+ ;
  "   Elapsed : ",seconds() - start

Cetin