>>>PS: Moving fields to different positions (ie: exchange yearOrdered with salesman or add to salesman or move to first position etc) show different summaries. Also simplest pivot show a single "data" item (the numbers) while you could add multiple calculations (like you can show sum, count, frequency, percentage of an item instead of just quantity sold). Click a number and + from toolbar:)
>>>
>>>Cetin
>>
>>Hi Cetin,
>>
>>
>>IF .F.
>> Perhaps you know how to do this. First create a cursor with string fields, some of which contain numbers as strings (for
>> example " 5.00") and other contain formulas as strings (for example "=SUBTOTAL(9,H4:H10)")
>>
>> Next, send the cursor to Excel via ADODB as before.
>>
>> Now, how do you convert the strings to Excel numbers and formulas from VFP using automation?
>>ENDIF
>>
>>
>>Found a simpler approach:
>>1) Insert subtotal lines directly in cursor in appropriate place: values not formulas,
>>2) Export cursor to Excel as usual
>>3) With automation, create outline as explained in this page:
http://www.ozgrid.com/Excel/outlining-manual.htm>>
>>Thanks,
>>
>>Alex
>>
>>P.S.
>>Just realized that if I want to use autofilter technique with a line of subtotals the intermediate subtotals (which are values and not formulas in this approach) will be double counted. That doesn't happen if intermediate subtotals are formulas. Hmmm...
>
>You mean something like this?
>
Select Cust_id, o.Order_id, Product_Id, Unit_price, Quantity ;
> from (_samples+'data\orders') o inner Join (_samples+'data\orditems') oi On o.Order_id = oi.Order_id ;
> order By Cust_id, o.Order_id ;
> into Cursor myOrders
>
>Dimension laSubtotal[2]
>laSubtotal[1] = 4
>laSubtotal[2] = 5
>
>#include "xlConstants.h"
>oExcel = Createobject("excel.application")
>With oExcel
> .Workbooks.Add
> .Visible = .T.
> * Place orders data 2 rows below Employee data
> * and name the range as 'Orders'
> PasteAtLocation(oExcel, 'myOrders','A1','Orders')
>
> *!* Subtotal grouping by customer then by order
> With .ActiveWorkBook.ActiveSheet
> .Range('A1').Select
> .Range('Orders').Subtotal(1, xlSum, @laSubtotal)
> .Range('Orders').Subtotal(2, xlSum, @laSubtotal,.F.,.F.,.F.)
> .UsedRange.Columns.AutoFit
> .Outline.ShowLevels(3)
> Endwith
>Endwith
>
>Function PasteAtLocation
> Lparameters toExcel, tcAlias, tcRange, tcRangeName
> Local lcTemp,ix,lcHeaders
> lcTemp = Sys(2015)+'.tmp'
> Select (m.tcAlias)
> Copy To (m.lcTemp) Type Delimited With "" With Tab
> lcHeaders=''
> For ix=1 To Fcount()
> lcHeaders = m.lcHeaders + Iif(Empty(m.lcHeaders),'',Chr(9)) + Field(m.ix)
> Endfor
> _Cliptext = m.lcHeaders + Chr(13) + Chr(10) + Filetostr(m.lcTemp)
> Erase (lcTemp)
> With toExcel.ActiveWorkBook.ActiveSheet
> .Range(m.tcRange).PasteSpecial()
> toExcel.Selection.Name = m.tcRangeName
> .Range(m.tcRangeName).Rows(1).Orientation = 90
> Endwith
>Endfunc
>
Cetin
Absolutely Great :))
Will find very good use for this.
Thks+++
Sergio