Hi Cetin,

For me personally, the first solution may be the better solution because I cannot expect that the user does have VFPOLEDB.DLL. And I don't want all the hassle of distributing and registering it either. Unless there is a clear advantage of course.

But now that I do have your attention :)...
I'm in search of the Excel method or property that will maximize the workbook. Do you know how to maximize it?

>:)
>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