>>> 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
>
>>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
>
>Yes, actually. The amazing thing is that in the case of my cursor example, which contains numbers as strings and formulas as strings, PasteAtLocation() converts the string to values or formulas, as appropriate. You don't have to add the SUBTOTAL()s by automation.
>
>How did you find that out? What caused Excel to do the conversion? Maybe going through a file? Maybe [Delimited With "" With Tab]? Maybe going through _cliptext.
>
>(Lightbulb!) It is likely that Excel makes strong effort to interpret text pasted from clipboard as was probably intended.
>
>Thank you.
>
>Alex
>
>P.S. Cesar and Srdjan, this is important information for what we are trying to do.
How did I found out? I don't know, I knew it for a long time I didn't even think how I happened to learn it:) Actually I have posted another version of that code years ago:
Re: Drill down in excel Thread #
1031781 Message #
1032103Looking at title and your "drill down" I see that my main problem was in understanding what you want:)
PS: It is PasteSpecial doing the job by the way. I read the documentation once upon a time and as I remember you could paste everyting, just values etc.
Cetin