Local oExcel as 'Excel.Application'
oExcel = CreateObject('Excel.Application')
oExcel.Workbooks.Add
oExcel.Visible = .t.
Wait window timeout 2
oExcel.WindowState= -4137 && xlMaximized

Cetin

>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