Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Multi-spreadsheets Via VBA`
Message
From
09/12/1998 12:24:26
Cetin Basoz
Engineerica Inc.
Izmir, Turkey
 
 
To
09/12/1998 11:14:14
Robert Byrd
National Association of Homebuilders
Washington, District of Columbia, United States
General information
Forum:
Visual FoxPro
Category:
Other
Miscellaneous
Thread ID:
00164289
Message ID:
00165636
Views:
15
>>>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
>
>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
Bob,
Code works as is provided you give the tablename as parameter. datatoclip() is a VFP function and applies to application object, _VFP. Here is part of VFP help :

Copies a set of records as text to the Clipboard.

Syntax

ApplicationObject.DataToClip([nWorkArea | cTableAlias]
[, nRecords] [, nClipFormat])

ie:
use home()+"samples\data\customer"
application.datatoclip("customer",5,3) && Copy 5 recs to clipboard with tabs
? _cliptext

Cetin
Çetin Basöz

The way to Go
Flutter - For mobile, web and desktop.
World's most advanced open source relational database.
.Net for foxheads - Blog (main)
FoxSharp - Blog (mirror)
Welcome to FoxyClasses

LinqPad - C#,VB,F#,SQL,eSQL ... scratchpad
Previous
Reply
Map
View

Click here to load this message in the networking platform