* Do SQL select ... * Set lcRows and lcCols lcRows = str(reccount()+1) && +1 header lcCols = str(fcount()) * Send data to excel lcXLSFile = ... copy to (lcXLSFile) type xls * Open data in Excel and name sheet - ie: "PivotData" oExcel = createobject("Excel.Application") with oExcel .WorkBooks.open(lcXLSFile) with .activesheet .Name = "PivotData" *... endwith *... endwith *... FUNCTION DoCreatePivots DIMENSION aPivotTables[2,4] aPivotTables[1,1] = "Number of Sign-ins" && Data Sheet name aPivotTables[1,2] = "nLogins" && Field to use in calc aPivotTables[1,3] = xlSum && Formula to use in calc aPivotTables[1,4] = "#,##0" && Number format to use in pivot table aPivotTables[2,1] = "Sign-in Session Times" aPivotTables[2,2] = "Period" aPivotTables[2,3] = xlSum aPivotTables[2,4] = "#,##0.0" DIMENSION taRowFields[1] && Excel pivot tables could hold more than one field for row, column, page DIMENSION taColumnFields[1] && VBA array is like a VFP array so use arrays instead of static varnames DIMENSION taPageFields[1] && In future we might want to pass more than one field for one of these && ie: taPageFields[2] and values "Week", "cDay" would make cDay a page field && then we could use Column for something else taRowFields[1] = "cLoginHour" taColumnFields[1] = "cDay" taPageFields[1] = "Week" =_Pivot2Excel("PivotData","R1C1:R"+lcRows+"C"+lcCols, @taRowFields, @taColumnFields, @taPageFields, @aPivotTables) **************************** * Pivot creator **************************** FUNCTION _Pivot2Excel LPARAMETERS tcDatabaseSheetName, tcDataRange,taRowFields, taColumnFields, taPageFields, taPivotTables * Pivot all WITH oExcel.ActiveWorkBook FOR ix = 1 to alen(taPivotTables,1) =_CreatePivot(tcDatabaseSheetName, tcDataRange, @taPivotTables, @taRowFields, @taColumnFields, @taPageFields) ENDFOR ENDWITH FUNCTION _CreatePivot LPARAMETERS tcDatabaseSheetName, tcDataRange, taPivotTables, taRowFields, taColumnFields, taPageFields WAIT window nowait "Creating pivot table " + taPivotTables[ix,1] .Sheets(tcDatabaseSheetName).activate .ActiveSheet.PivotTableWizard(xlDatabase, ; tcDatabaseSheetName+"!"+tcDataRange,"", taPivotTables[ix,1]) && Wizard sets Range and name WITH .ActiveSheet.PivotTables(taPivotTables[ix,1]) IF !empty(taPageFields[1]) .AddFields( @taRowFields, @taColumnFields, @taPageFields ) && Fields added ELSE .AddFields( @taRowFields, @taColumnFields ) ENDIF .PivotFields(taPivotTables[ix,2]).Orientation = xlDataField && tcDataField set as data field IF taPivotTables[ix,3] # xlSum .PivotFields("Sum of "+taPivotTables[ix,2]).Function = taPivotTables[ix,3] && Calculation method set ENDIF ENDWITH WITH .ActiveSheet .name = taPivotTables[ix,1] .PivotTables(taPivotTables[ix,1]).PivotSelect( "", xlDataOnly) IF type("taPivotTables[ix,4]") = "C" and !empty(taPivotTables[ix,4]) oExcel.Application.Selection.NumberFormat = taPivotTables[ix,4] ENDIF .PivotTables(taPivotTables[ix,1]).PivotSelect( "", xlDataAndLabel) .Range("A1").AutoFormat(xlRangeAutoFormatColor2) .PivotTables(taPivotTables[ix,1]).PivotSelect( "", xlOrigin) ENDWITH **************************** * Pivot creator ****************************This was part of an Excel automation project and Excel creating about 5 pivots + charting about 12 all take 12-13 secs on my K6-2 333. Only thing that I couldn't overcome yet is the reccount() limit that's passed to excel. w/o passing as xls it's nightmare to wait excel to get data via ODBC and bomb :(