>>>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,
Hello again Cetin & Erik,
The selection object also works:
loExcel = CREATEOBJECT('excel.application')
loSheet = loExcel.Application.Workbooks.Open(lcSpreadSheet, 0)
DIMENSION laArray[1]
laArray[1] = 14
loSheet.Application.ActiveWindow.Selection.Subtotal(5, xlsum, @laArray, 1, 0, 1)
Thanks!
Vern