>You're welcome. A working sample is always a starting point:)
>
>

>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.lcSQL1 = Chrtran(m.lcSQL,';'+Chr(13)+Chr(10),'')+;
>  ' where c.cust_id < "C"'
>m.lcSQL2 = Chrtran(m.lcSQL,';'+Chr(13)+Chr(10),'')+;
>  ' where c.cust_id >= "C"'
>
>
>lcConnPath = _samples+'data'
>
>Alines(laRowFields,'prod_name')
>Alines(laColumnFields,'Employee')
>Alines(laPageFields,'cust_id')
>
>#include xlConstants.h
>oExcel = Createobject('Excel.Application')
>With oExcel
>  .Workbooks.Add
>  .Visible = .T.
>  With .ActiveWorkbook
>    oRange1 = .ActiveSheet.Range('A3')
>    oRange2 = .WorkSheets('Sheet2').Range('A3')
>
>    With .PivotCaches.Add(xlExternal)
>      .Connection  = "OLEDB;Provider=VFPOLEDB;Data Source="+m.lcConnPath
>      .CommandType = xlCmdSQL
>      .CommandText = m.lcSQL1
>      .CreatePivotTable(m.oRange1, "myPivTable")
>      .CreatePivotTable(m.oRange2, "myPivTable2")
>    Endwith
>    With .ActiveSheet.PivotTables("myPivTable")
>      .AddFields(@laRowFields, @laColumnFields, @laPageFields)
>      .PivotFields('Quantity').Orientation = xlDataField
>    Endwith
>
>    Alines(laRowFields,'cust_id')
>    Alines(laColumnFields,'Employee')
>
>    With .WorkSheets('Sheet2').PivotTables("myPivTable2")
>      .AddFields(@laRowFields, @laColumnFields)
>      .PivotFields('Quantity').Orientation = xlDataField
>    Endwith
>  Endwith
>  Wait Window Timeout 10
>  ?? Chr(7)+Chr(7) && Changing data in cache
>  With .ActiveWorkbook.PivotCaches(1)
>    .CommandText = m.lcSQL2
>    .Refresh
>  Endwith
>Endwith
>

In this sample there is a single cache and 2 pivots (sheet 1 and 2) and 2 SQLs. 2nd kicks in after a 10 seconds delay in background (why? I don't know, just wrote for sampling refreshing the cache and nobody was changing the data so I did by changing the SQL).
>PS: In this sample the data is small but in the past I have tried this with data around 200-300K records (non xtabbed rows) on an AMD 2500+ and the respond time were around 2-3seconds.
>Cetin
Thanks Cetin
I guess the starting point is that I was out of my depth - Tamars book got me going some years ago on Pivot Tables and once I had a working model then that learning curve stopped. So when life changed with Vista/Excel2007 it all stopped.
I am working my way through with your help, PivotCaches seems to be the core of Pivot Tables so this week end I am focused on trying to understand more in that area, many thanks for your help - will no doubt be back with more questions - will keep you informed.
Regards