Thanks Cetin (that was quick)!

Yes the Excel spreadsheet has data in it. So thats not the issue:(

I am also confused by the group by parameter. I am not at all sure how this relates. Is it the column number that is being used instead of a Letter?




I am also confused by the group by parameter. I am not at all sure how this relates. Is it the column number that is being used instead of a Letter?


>In that code there is no check if usedrange has data. Comment subtotaling line and check what you get.
>Cetin
>
>>Hello Cetin
>>
>>when I run your code sample below I get this error
>>
>>OLE Dispatch exception code 0 from Microsoft Excel: Unable to get the Subtotal property of the range class..
>>
>>Any ideas?
>>
>>
>>>>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] && This is our offsets array
>>>totflds[1]=5 && We want Quantity and Extended to be totalled - 5,6th positions
>>>totflds[2]=6
>>>oX = createobject('Excel.Application')
>>>With oX
>>>  .Workbooks.Open(lcXLS)
>>>*!*		with .ActiveWorkbook.ActiveSheet
>>>*!*			.UsedRange.Select
>>>*!*			.Application.Selection.Subtotal(2, xlSum, @totflds, .T.)
>>>*!*		endwith
>>>  .ActiveWorkbook.ActiveSheet.UsedRange.Subtotal(2, xlSum, @totflds, .T.)
>>>  .visible = .t.
>>>Endwith

Cetin