One more question on the subtotal thing. Can I make the level collapsed to give me a summary report in essance with drill down capabiulity


Peter


>>How can I do the following in VFP:
>>

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

>>I have
>>
>>

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

>>
>>
>>Thanks alot
>>
>>
>>Peter
>
>
>

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

Cetin