>>>>>I have been trying to automate the creation of an excel pivottable from vfp. The basic idea is to create an excel session, load a saved worksheet, and than create a pivottable form the worksheet data. I'm fine upto creating the pivottable. I can't get a handle on the correct command syntax required for excels pivottablewizard. Any help would be appreciated.
>>>>
>>>>I'm working the same thing now. It's Kicking my backside! I've finally gotten the range to take, but I keep getting a syntax error when I try to add fields.
>>>>
>>>>Man I hope someone comes to our rescue...
>>>>
>>>>Jeff
>>>
>>>Did you try to use Macro recording to record the necessary sequence of actions in Excel and then see the syntax of commands it creates for the macro?
>>>
>>>Nick
>>
>>Hi Nick!
>>I did use the macro recorder, but was unable to adapt the commnads to automate via VFP. Though this project is in VFP 5.0, I took apart the pivot table wizard found in VFP 6.0 foundation class. The main diffence is the the VFP 6 pvtable wizard assumes you load the data by ODBC query via excel, while I want to load the data into the worksheet via an excel worksheet. Even using the commands the 6.0 wizard uses to set the pivot tables row, colum, page and data fields cause syntax errors. Very Frustrating!!!!!!
>>
>>Aside to Jeff. Your one up on me, getting the range to take. Could you let me the syntax to accomplish this?
>>
>>Tom
>
>Tom,
>
>What A Pain this has turned out to be!!!
>SourceType=
>xlConsolidation=3
>xlDatabase=1
>xlExternal=2
>xlPivotTable=-4148
>
>ox.activesheet.PivotTableWizard(1,"'Raw Data'!R1C1:R"+(sayit)+"C14","'Totals'!R1C1:R3C1","PT1")
>
>I switched gears and now have this being done from an ASP Page on the client side using VBScript. I could never get the addfield to work without syntax errors in Fox. If you figure it out PLEASE let me know.
>
>Here's the ASP Code that works.
>had to manually add each field instead of array command
>first place addes rowfield
>second place addes columnfields
>third place addes pagefields
>fourth place 1 if for add/don't replace 0 for replace
>ox.Worksheets("Totals").PivotTables("PT1").AddFields "RF1",,,1
>ox.Worksheets("Totals").PivotTables("PT1").AddFields "RF2",,,1
>ox.Worksheets("Totals").PivotTables("PT1").AddFields "RF3",,,1
>ox.Worksheets("Totals").PivotTables("PT1").AddFields "Data",,,1
>ox.Worksheets("Totals").PivotTables("PT1").AddFields ,,"PF1",1
>ox.ActiveSheet.PivotTables("PT1").PivotFields("Credit").Orientation = 4
>ox.ActiveSheet.PivotTables("PT1").PivotFields("Debit").Orientation = 4
>
>Hope to here from you soon on how to do this from FOX
>
>HTH
>Jeff
>HTH


Hi Jeff, I finally got it to work late last friday. Here is the code I hope it helps. - Tom

*******************************************************
LOCAL xlactivesheet, lcWorksheet, lcRange

lcWorkSheet =this.cWorksheet
lcRange =lcWorksheet+"!R1C1:R"+ALLTR(STR(this.nrows))+"C4"
lcFile =this.cxlfile

IF ! this.getexcelsession() OR TYPE('this.oxlapp') # "O"
RETURN .F.
ENDIF

Wait Window NOWAIT "Creating PivotTable..."

* - add a new workbook
this.oxlapp.workbooks.Add

* Open the spreadsheet that contains the pivottable data
* created and exported to execl via the requerydata method
THIS.oxlapp.workbooks.OPEN(lcfile)

xlactivesheet =this.oxlapp.activesheet

IF THis.lhadoleerror
* Show something
ENDIF

* Set Ranges: Note Empty Parameter defaults to Sheet1 for pivottable location
xlactivesheet.pivotTableWizard(1, lcRange, "", "PivotTable1")

* add row field
this.oxlapp.worksheets("Sheet1").pivotTables('PivotTable1').AddFields("Product")

* Add column field
this.oxlapp.worksheets("Sheet1").pivotTables('PivotTable1').PivotFields("Type").orientation =2

* Add page field
this.oxlapp.worksheets("Sheet1").pivotTables('PivotTable1').PivotFields("Area").orientation =3

* add data field
this.oxlapp.worksheets("Sheet1").pivotTables('PivotTable1').PivotFields("Cost").orientation =4

Wait Clear

* show excel
this.oxlapp.visible =.T.