>I have a problem trying to translate a VBA statement into the VFP equivalent to manage an automation requirement. - any suggestione would be most welcome:
>
>**Code below is the VB for Applications syntax
>
>**Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(2), _
>** Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Sam,
I think this is macro recording. Macro recording uses VBA syntax with 'named parameters'. VFP uses 'positional parameters'. With named parameters style you could supply the parameters in 'any order' by assigning names to them as in your sample. Fortunately from Excel97 and up help gives the syntax with their correct 'positions'. Suntotal syntax reads :
expression.Subtotal(GroupBy, Function, TotalList, Replace, PageBreaks, SummaryBelowData)
With this syntax your call would translates to VFP :
* Step 1 - fill the values with their names to corect positions
Selection.Subtotal(GroupBy:=1, Function:=xlSum, TotalList:=Array(2), ;
Replace:=True, PageBreaks:=False, SummaryBelowData:=True)
* Step 2 - Remove unwanted parameter names and convert things like true/false
* to VFP counterpat
Selection.Subtotal(1, xlSum, Array(2), .T., .F., .T.)
* Step 3 - Array(2) is a VBA function, supply VFP counterpart
dimension arrFlds[1]
arrFlds[1]=2
Selection.Subtotal(1, xlSum, @arrFlds, .T., .F., .T.)
* Step 4 - Omit optional params that are already at their defaults
* xlSummaryBelow is a constant with value 1 and therefore .t./.f.
* would work. However it's better to use value there. In this case
* xlSummaryBelow is default and optional, will omit
* First 3 are 'required' params
Selection.Subtotal(1, xlSum, @arrFlds, .T.)
* Note : If say we'd omit 3rd parameter and keep 4th
* function would look like
* SomeFunction(Par1, Par2, ,Par4)
* Step 5 - Be sure what this function applies to and use it VFP style
Selection.Subtotal(1, xlSum, @arrFlds, .T.)
* Would fail since no Selection object exists
* Assuming excel.application is oExcel and we're in a with..endwith
* pointing to an object of oExcel
* ie: with oExcel.Activeworkbook.ActiveSheet
* Subtotal applies to 'Range' and 'WorkSheetFunction'
* 'Selection' is a 'range' object but always keep in mind
* 'selection' is a member of oExcel itself.
* oExcel.Selection would work but here we're at a deeper level
* (oExcel.Activeworkbook.ActiveSheet). At any level .Application
* points to oExcel itself. So we'd say :
.Application.Selection.Subtotal(1, xlSum, @arrFlds, .T.)
Below code demonstrates this with another shortcut 'range' object :
#Define xlSum -4157
#Define xlSummaryAbove 0
#Define xlSummaryBelow 1
#Define xlSummaryOnLeft -4131
#Define xlSummaryOnRight -4152
Select *, quantity*unit_price as 'extended' ;
from orditems into cursor crsTemp
lcXLS = sys(5)+curdir()+'xlsubtot.xls'
Copy to (lcXLS) type xls
Dimension totflds[2]
totflds[1]=5
totflds[2]=6
oX = createobject('Excel.Application')
With oX
.Workbooks.Open(lcXLS)
.ActiveWorkbook.ActiveSheet.UsedRange.Subtotal(2, xlSum, @totflds, .T.)
.visible = .t.
Endwith
Cetin