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