>>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.
PasteAtLocation(oExcel, 'myOrders','A1','Orders')
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