I’m very thankful to you for taking out your time and replying me for my problem..

I’ve tried sample code (1st one) & went through your whole reply and I’ve many questions for the same…

1. Sample code is giving Ole Error at

lcHPB = .HPageBreaks(1).Location.Address(.f.,.f.,xlR1C1)

Pls note that I’m using Off 95.. Is this code designed for 97 ?? Pls advs…

2. For sample 1, u’ve written "its a nightmare to do it that way… & VFP report writer is more effective", Pls note that there are few reports in my current project which users wants to transfer in Excel… VFP reports are giving page wise totals, so same thing is req. in Excel… Pls advs. Ur comments…


3. I’m not that much familiar with Pivot table (never used it before), As far as I know, its used for displaying Summary Data (Pls correct me if I’m wrong). As long as it is solving my problem, I don’t mind using it… But still it was not clear to me How it can be used in this situation ??

4. I’ve haven’t tested the Pivot table code.. Looks like u’ve done extensive programming in this field.. (Speaking frankly, half of the code went abv my head).. I’ll try that code and advs. The results…


Eagerly awaiting for your reply..

Thnks n Rgds,
Vinod



>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