:)
Here is another sample using OLEDB driver for huge data (would work with rowcount over 64K) :

TEXT to m.lcSQL noshow
Select c.cust_id, c.company, ;
  o.order_id, o.order_date, ;
  e.First_Name-(' '+e.Last_Name) As Employee, ;
  oi.line_no, p.prod_name,;
  oi.unit_price, oi.quantity;
  FROM  testdata!customer c ;
  INNER Join testdata!orders o;
  ON  c.cust_id = o.cust_id ;
  INNER Join testdata!Employee e ;
  ON  o.emp_id = e.emp_id ;
  INNER Join testdata!orditems oi;
  ON  o.order_id = oi.order_id ;
  INNER Join testdata!products p ;
  ON  oi.product_id = p.product_id ;
  order By p.prod_name
ENDTEXT
m.lcSQL = Chrtran(m.lcSQL,';'+Chr(13)+Chr(10),'')

lcPivotTableName = 'myCustomers'
lcConnPath = Sys(5)+Curdir()
lcConnStr = [Provider=VFPOLEDB.1;Data Source=]+lcConnPath

Alines(laRowFields,'prod_name')
Alines(laColumnFields,'Employee')
Alines(laPageFields,'cust_id')

#include xlConstants.h
oExcel = Createobject('Excel.Application')
With oExcel
  With .Workbooks.Add
    _CreatePivotTable(.ActiveSheet.Range('A3'),;
      m.lcPivotTableName,m.lcSQL,;
      m.lcConnStr)
    With .ActiveSheet.PivotTables(lcPivotTableName)
      .DisplayErrorString = .T.
      .MergeLabels = .T.
      .SmallGrid = .F.
      _AddPivotFields(@laRowFields, @laColumnFields, @laPageFields)
      .PivotFields('Quantity').Orientation = xlDataField
    Endwith
  Endwith
  .Visible = .T.
Endwith

Function _CreatePivotTable
Lparameters toDestination,tcPivotTableName, tcSQL,tcConnStr
With .PivotCaches.Add(xlExternal)
  .Connection  = 'OLEDB;'+m.tcConnStr
  .CommandType = 2 && xlCmdSQL
  .CommandText = m.tcSQL
  .Maintainconnection = .F.
  .EnableRefresh = .F.
  .BackGroundQuery = .T.
  .CreatePivotTable(m.toDestination, m.tcPivotTableName)
Endwith
Endfunc

Function _AddPivotFields
Lparameters taRowFields, taColumnFields, taPageFields
If !Empty(taPageFields[1])
  .AddFields( @taRowFields, @taColumnFields, @taPageFields ) && Fields added
Else
  .AddFields( @taRowFields, @taColumnFields )
Endif
Endfunc

Cetin

>Cetin,
>
>After several months I once more picked up 'my problem', after some time staring at it went to the UT, searched a little and found out that I personally had started a tiny thread, exactly describing my problem. Moreover, I found out that you had given an answer that indeed contained the solution! So, I have given you the 3 stars after all. Thanks! :)
>
>>>Hi to All (but I hope that MS people are reading this too and feel urged to respond),
>>>
>>>I have written a vfp-application that's installed using Inno Setup.
>>>It contains a feature to create a pivot table in Excel.
>>>The feature is disabled if Excel is not on the PC (check is done with CheckServer('excel.application').
>>>The pivot table is created prior to calling Excel, using vfp7\ffc\pivtable.vcx. This vcx uses vfp7\ffc\automate.vcx.
>>>Automate.vcx contains the custom class Automate. The class does an ODBC check on init. On client machines this check produces the message..
>>>
>>>Could not find FoxPro Files ODBC driver. Check to see if it is installed.
>>>
>>>The consequence is that the custom class is *not* instantiated and Excel can therefore not be called.
>>>
>>>Questions:
>>>
>>>Do I really have to use ODBC in order to present a pivot table to Excel???
>>>
>>>If YES, what must I distribute, and how can I activate that all automatically?
>>>
>>>If NO, is there an alternative for the pivtable.vcx?
>>
>>Peter,
>>To create a pivot table you don't need ODBC driver (I don't use that VCX). You could use OLEDB driver or no driver at all (depends on if you've data that'd fit on a spreadsheet if you'd use no driver)
>>If that class is using ODBC somewhere in code there should be one reading :
>>PivotCaches.Add or QueryTables.Add and "ODBC: ..." following it. Change call there to "OLEDB: ..." with other needed changes.
>>For with no driver option copy to type fox2x, csv or xls (depending on data size and VFP version). Then use xlDatabase as source. ie:
>>
>>

>>* Pivottable sampling for VFP3-5 - Excel97
>>* Current wizard doesn't work for Excel97
>>* VFP6 users probably don't need this
>>* This is a simple sample and dummy with regard to
>>* data choosen for pivot table
>>* Pivot tables are kind of a crosstab tables
>>* Data choosen is testdata!orditems
>>* First a cursor is constructed having order_date, prod_name,
>>* employee_name, customer.company and quantity
>>* Cursor is copied to an XL5 file - faster then using ODBC
>>* from Excel or writing directly to Excel with datatoclip()
>>* Unless data is huge
>>* XLS is opened
>>* company set as page field
>>* employee set as column field
>>* Product and Dates are set as row field to demonstrate multipl fields setting
>>* When you remove dates you get sum() grouped by product
>>
>>SET safety off
>>SET century on
>>#DEFINE DataPath ""
>>
>>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")
>>    dimension flist[2]
>>    flist[1]="prod_name"
>>    flist[2]="dates"
>>      .AddFields( @flist, "employee","company" ) && Fields added
>>* Add rowfields w/o an array
>>*      .AddFields( "prod_name", "employee", "company" ) && Fields added
>>*      .PivotFields("dates").Orientation = xlRowField
>>&& Dates added as second row field
>>* Add rowfields w/o an array
>>      .PivotFields("quantity").Orientation = xlDataField
>>&& Quantity set as data field
>>      .PivotFields("company").Subtotals(1) = .t.
>>      .PivotFields("company").Subtotals(1) = .f.
>>    ENDWITH
>>  ENDWITH
>>ENDWITH
>>

>>Cetin