>Hi all,
>
>With the help of a message posted by Cetin last March, I've been able to build an Excel Pivot from within VFP.
>
>A slight problem remain:
>

>WITH thisform.oExcelApplication
>  .range("A1:" + thisform.lcColName + thisform.lcRows).Select
>  .ActiveSheet.PivotTableWizard( XLDATABASE, ;
>    lcJustStemExcelSheet +"!R1C1:R" + thisform.lcRows + "C" +  thisform.lcCols, ;
>    "", ;
>    "Tableau_croisé_dynamique")
>  WITH .ActiveSheet.PivotTables("Tableau_croisé_dynamique")
>*!*	    .AddFields( Array( "cZipLoc", "cNom"), ;
>*!*	      "nMonthVersement", ;
>*!*	      "nYearVersement")
>.AddFields(  "cNom", ;
>      "nMonthVersement", ;
>      "nYearVersement")
>    .PivotFields( "yVersement").Orientation = XLDATAFIELD
>  ENDWITH
>ENDWITH
>

>
>Watch the .addfields method. It accepts 3 parameters. But the first one can be an array of paramters; in VBA, the code would be:
>
>AvtiveSheet.PivotTables( "Tableau_croisé_dynamique").AddFields Rowfields:=Array( "cZipLoc", "cNom"), ColumnFields="nMonthVersement", PageFields = "nYearVersement"
>
>How could I translate this in VFP? I've tried with variations of brackets ", ', [, but no luck till now.
>
>José
Hi Jose,
I haven't been around for a while (had a car accident - good we're all well). Upon return I saw your message and I think this one is your "another post" :)
You won't believe this but what excel waits as an array is what we know as "array" in VFP :) And yet there is another workaround that also works :

SET safety off
SET century on
#DEFINE DataPath home()+"samples\data\"

SELECT b.emp_id ,a.company, b.order_date, d.prod_name, c.quantity  ;
  FROM  (DataPath+"customer") a;
  INNER JOIN (DataPath+"orders") b;
  INNER JOIN (DataPath+"orditems") c;
  INNER JOIN (DataPath+"products") d;
  ON  d.product_id = c.product_id ;
  ON  c.order_id = b.order_id ;
  ON  b.cust_id = a.cust_id;
  order by 1,2,3 ;
  into cursor myCursor ;
  nofilter

SELECT a.first_name + a.last_name as Employee, ;
  b.company, b.prod_name, b.quantity, b.order_date as "dates" ;
  from (DataPath+"employee") a;
  INNER join  myCursor b;
  on a.emp_id = b.emp_id ;
  into cursor pivotdata

COPY to pivotdata type xl5	&& Copied to xls file
lcRows = ltrim(str(reccount()+1)) && Including header line
lcCols = ltrim(str(fcount()))
* Pivot sample
oExcel = createobject("Excel.application")
#DEFINE xlDatabase 1

#DEFINE xlRowField 1
#DEFINE xlColumnField 2
#DEFINE xlPageField 3
#DEFINE xlDataField 4

WITH oExcel
  .visible = .t.
  .workbooks.open(sys(5)+curdir()+"pivotdata.xls") && Open saved xls
  WITH .ActiveWorkBook
    .Sheets("Pivotdata").activate
    .ActiveSheet.PivotTableWizard(xlDatabase, ;
      "pivotdata!R1C1:R"+lcRows+"C"+lcCols,"", "PivotTable1") && Wizard sets Range and name
    WITH .ActiveSheet.PivotTables("PivotTable1")
<color=blue><b>    dimension flist[2]
    flist[1]="company"
    flist[2]="dates"
      .AddFields( @flist</b>, "prod_name", "employee" ) && Fields added
* Or as an alternative w/o array passing
*  .AddFields( "company", "prod_name", "employee" ) && Fields added
*  .PivotFields("dates").Orientation = xlRowField   && Dates added as second row field
* other elements are added using orientation</color>
      .PivotFields("quantity").Orientation = xlDataField && Quantity set as data field
    ENDWITH
  ENDWITH
ENDWITH

Cetin