>>>Hi Erik,
>>>
>>>>>I would like to call the Excel SubTotal method thru a VFP ActiveX Automation call.
>>>>>
>>>>>I understand how to pass all the parameters the method is looking for except one that is an array of the column numbers to subtotal on.
>>>>>
>>>>>Can anyone suggest how to pass arrays from VFP to Excel that I can use for this purpose?
>>>>>
>>>>>Thanks!
>>>>
>>>>Does not passing the array by reference work?
>>>>
>>>>exPression.SubTotal(nGroupBy, nFunction, @laTotalList)
>>>
>>>Well, I get an OLE error when I tried that:
>>>DIMENSION laArray[1]
>>>laArray[1] = 14
>>>loSheet.ActiveSheet.Subtotal(5, xlsum, @laArray, 1, 0, 1)
>>>
>>>Can you see anything wrong with that?
>>>
>>>Thanks!
>>Vern,
>>Passing arrays by ref. as Erik suggested works. It's not array causing OLE error there. Subtotal applies to range object. For you to use subtotals you must have list columns with headers (like a VFP table). Here is a sample :
>>DIMENSION laArray[3]
>>laArray[1] = 2
>>laArray[2] = 3
>>laArray[3] = 5
>>oExcel = createobject("excel.application")
>>=rand(-1)
>>WITH oExcel
>> .Workbooks.add
>> .range("A1:K1").cells.value = "Header"
>> FOR lnColumn = 1 to 11
>> FOR lnRow = 2 to 7
>> IF lnColumn = 1
>> .cells(lnRow,lnColumn).value = "Group"+ltrim(str(int(rand()*lnRow+1)))
>> ELSE
>> .cells(lnRow,lnColumn).value = int(rand()*lnColumn) + lnRow
>> ENDIF
>> ENDFOR
>> ENDFOR
>> #define xlSum -4157
>> .range("A8").Subtotal(1, xlSum, @laArray, 1, 0, 1)
>> .visible = .t.
>>ENDWITH
Cetin
>
>Hi Cetin,
>
>You are correct, using the Range object solved my problem. The Excel macro that I recorded used the Selection object. But I was unable to get that to work.
>
>Thank you both for your help!
>
>Cheers,
Vern,
In macro recording things sometimes get hard. If you want to use "selection" then it applies to application object :
oExcel.Application.Selection works but not oExcel.Selection.
Tip : Whenever it barks unknown name, not member, cannot get class.. etc (and you're sure you used it correctly) first try oExcel.Application then oExcel.Application.object (same for other ActiveX). Sometimes solves.
Cetin