Pls note that I've tested pivot table code, but it is giving Ole error in this line

.PivotTables(taPivotTables[ix,1]).PivotSelect( "", xlDataOnly)


Pls advs. ur comments..


>Vinod,
>I kept my promise :) Well I checked it and it's really cumbersome especially if you don't set PrintArea. Here is some code like previous :)

#include "xlConstants.h"
>USE home()+"samples\data\customer"
>Application.datatoclip("customer",reccount(),3)
>PUBLIC oExcel
>oExcel = createobject("Excel.application")
>Clear
>WITH oExcel
>  .WorkBooks.Add
>  WITH .ActiveSheet
>    .Range("D20").select
>    .Paste
>    lcLastCell = .Range("A1").SpecialCells(xlLastCell).Address(.f.,.f.,xlR1C1)
>    lcHPB = .HPageBreaks(1).Location.Address(.f.,.f.,xlR1C1)
>    lcVPB = .VPageBreaks(1).Location.Address(.f.,.f.,xlR1C1)
>    lcNewRange = chrtran(lcHPB+substr(lcVPB,3),"[]","")
>    .Application.Goto(lcNewRange)
>  ENDWITH
>  .visible = .t.
>ENDWITH
>? "PrintArea ",.Pagesetup.PrintArea && Empty - no printarea set
>*  	oExcel.ActiveSheet.PageSetup.PrintArea = "A1:Z20"
>*? "PrintArea ",.Pagesetup.PrintArea
>? "Horizontal pagebreak at ", lcHPB
>? "Vertical pagebreak at ", lcVPB

IMHO it's a nightmare to do it this way. If you would do it like that IMHO VFP report writer is more effective (open to discussion:).
>But I suggest pivot tables and that's where really Excel comes very handy. Here is a detailed sample code (more than sample I think) for pivoting in Excel (in VFP6 -and VFP5- there is a pivot wizard that I don't use. VFP5 wizard bombs with Excel97 and that was the main reason I wrote mine). Code is long but just give it a try after modifying the includefile path&name :

#include "d:\acutrack\xlConstants.h"	  && All xl97 constants
>tcCursorName = "mytestcursor"
>=CreateTestCursor(tcCursorName, 10000) && Create test cursor tcCursorName with n records
>
>SELECT "'"+padl(ltrim(str(week(dProcessed,0,0))),2,"0") as "Week", ;
>  padr(cdow(dProcessed),9) as "WeekDay", ;
>  cDept as "Department", ;
>  cEmployee as "Employee", ;
>  sum(nAmount) as "DayAmt" ;
>  from (tcCursorName) ;
>  group by 1,2,3,4 ;
>  nofilter ;
>  into cursor crsMyCursor
>
>DIMENSION aPivotTables[2,4]
>aPivotTables[1,1] = "My First Pivot Sheet"	&& Sheet name
>aPivotTables[1,2] = "DayAmt"			&& 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] = "My Second Pivot Sheet"
>aPivotTables[2,2] = "DayAmt"
>aPivotTables[2,3] = xlAverage
>aPivotTables[2,4] = "#,##0.0"
>
>* Excel pivot tables could hold more than one field for row, column, page
>* VBA array is like a VFP array so use arrays instead of static varnames
>DIMENSION taRowFields[2] && We want two fields to go in rows
>DIMENSION taColumnFields[1]
>DIMENSION taPageFields[1]
>
>taRowFields[1] = "Department"
>taRowFields[2] = "Employee"
>taColumnFields[1] = "WeekDay"
>taPageFields[1] = "Week"
>
>WAIT window nowait "Sending data to Excel..."
>
>lcTempExcelFile=sys(5)+curdir()+"X"+sys(2015)+".xls"
>COPY to (lcTempExcelFile) type xl5	&& Copied to xls file
>lcRows = ltrim(str(reccount()+1)) && Including header line
>lcCols = ltrim(str(fcount()))
>*** set the LOCALEID to English
>nlLocaleId=sys(3004)		&& Save local id
>=sys(3006,1033)				&& We will be sending instructions in English
>*** set the LOCALEID to English
>oExcel = createobject("Excel.application")
>WITH oExcel
>  .workbooks.open(lcTempExcelFile) && Open saved xls
>  .ActiveSheet.Name = "PivotData"
>&& Create pivot tables
>  =_Pivot2Excel("PivotData","R1C1:R"+lcRows+"C"+lcCols, ;
>    @taRowFields, @taColumnFields, @taPageFields, @aPivotTables)
>  .visible = .t. && Done - show
>  WAIT clear
>ENDWITH
>myHandle = fopen(lcTempExcelFile,12)
>DO while myHandle < 0
>  myHandle = fopen(lcTempExcelFile,12) && Try to open temp xls read-write
>ENDDO
>=fclose(myHandle) && Excel released it
>Release oExcel
>
>ERASE (lcTempExcelFile)
>
>**** Set the LocaleId to the previous value
>=sys(3006,val(nlLocaleId))
>**** Set the LocaleId to the previous value
>
>
>****************************
>* Excel routines start here
>****************************
>****************************
>* 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
>****************************
>* Return A, AA, BC etc noation for nth column
>FUNCTION _GetChar
>LPARAMETERS tnColumn && Convert tnvalue to Excel alpha notation
>IF tnColumn = 0
>  RETURN ""
>ENDIF
>IF tnColumn <= 26
>  RETURN chr(asc("A")-1+tnColumn)
>ELSE
>  RETURN 	_GetChar(int(iif(tnColumn % 26 = 0,tnColumn - 1, tnColumn) / 26)) + ;
>    _GetChar((tnColumn-1)%26+1)
>ENDIF
>
>****************************
>* Excel routines end here
>****************************
>
>***************************************************************
>* Support Routines - Generally first time use
>***************************************************************
>********************************
>* Test cursor creation
>********************************
>FUNCTION CreateTestCursor
>LPARAMETERS tcCursorName, tnReccount
>=rand(-1)
>
>CREATE cursor (tcCursorName) ;
>  (cDept c(3), cEmployee c(3), dProcessed d, nAmount i)
>
>SCATTER memvar
>FOR ix = 1 to tnReccount
>  = generaterandomvalues()
>  INSERT into (tcCursorName) from memvar
>  IF ix%5000 = 0
>    WAIT window nowait "Created "+ltrim(str(ix)) + "/" + ltrim(str(tnReccount))
>  ENDIF
>ENDFOR
>WAIT window nowait "Create done. Indexing... "
>
>FOR ix = 1 to fcount()
>  lcField = field(ix)
>  INDEX on &lcField tag (lcField)
>ENDFOR
>WAIT clear
>
>FUNCTION generaterandomvalues
>m.cDept			= "D"+padl(int(rand()*5),2,"0")
>m.cEmployee		= "E"+padl(int(rand()*10),2,"0")
>m.dProcessed	= date() - int(rand() * 800)  && 800 days randomly
>m.nAmount		= int(rand() * 100000)   && Near Bill ?

Hope this helps.
>Cetin