> Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(5), _
>        Replace:=True, PageBreaks:=True, SummaryBelowData:=True
>

>
>oExcel=CREATEOBJECT("Excel.Application")
>oExcel.visible= .T.
>
>oW=oExcel.Workbooks.Add(lcTemplate)
>ow.Worksheets[1].select
>
>oRange = oExcel.ActiveSheet.Range("A7:F"+TRANSFORM(6+lnRecCount))
>
>oExcel.ActiveSheet.paste(oRange)
>** now I want excel to subtotal by column a
>
>
> ****Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(5), _
>        Replace:=True, PageBreaks:=True, SummaryBelowData:=True
>

Select Cust_id, o.Order_id, Product_Id, Unit_price, Quantity ;
  from orders o inner join orditems oi on o.order_id = oi.order_id ;
  order by cust_id, o.order_id ;
  into Cursor crsTemp

lcXLSFile = Sys(5)+Curdir()+'myOrders1.xls'
Copy To (lcXLSFile) Type Xls

Close Databases all

Dimension laSubtotal[3]
laSubtotal[1] = 4 && Unit_price
laSubtotal[2] = 5 && Quantity
laSubtotal[3] = 6 && Will use later

#include "xlConstants.h"
oExcel = Createobject("excel.application")
With oExcel
  .Workbooks.Open(lcXLSFile)
  With .ActiveWorkbook.ActiveSheet
    lnRows = .UsedRange.Rows.Count && Get current row count
    lcFirstUnusedColumn = _GetChar(laSubtotal[3]) && Get column in Excel A1 notation
    * Instead of orders order_net field use Excel calculation for net prices
    .Range(lcFirstUnusedColumn+'2:'+;
      lcFirstUnusedColumn+Transform(lnRows)).FormulaR1C1 = ;
      "=RC[-2]*RC[-1]"
    .Range(lcFirstUnusedColumn+'1').Value = 'Extended Price' && Place header
    .Range('D:'+lcFirstUnusedColumn).NumberFormat = "$#,##0.0000" && Format columns

    * Subtotal grouping by customer then by order
   	.UsedRange.Subtotal(1, xlSum, @laSubtotal)
   	.UsedRange.Subtotal(2, xlSum, @laSubtotal,.f.,.f.,.f.)

    .UsedRange.Columns.Autofit && Autofit columns
  Endwith
  .Visible = .T.
Endwith

* Return A, AA, BC etc noation for nth column
Function _GetChar
  Lparameters tnColumn && Convert tnvalue to Excel alpha notation
  If tnColumn = 0
    Return ""
  Endif
  If tnColumn <= 26
    Return Chr(Asc("A")-1+tnColumn)
  Else
    Return 	_GetChar(Int(Iif(tnColumn % 26 = 0,tnColumn - 1, tnColumn) / 26)) + ;
      _GetChar((tnColumn-1)%26+1)
  Endif