Hello friends.
I am trying to do Subtotals and for this code:

oExcel:Selection:Subtotal(2,xlSum,Array(11),True,False,True)

The aplications gives an error of wrong data type.

If I do this in the excel file it works fine:

Sub Macro1()
'
' Macro1 Macro
'

'
    Selection.Subtotal GroupBy:=2, Function:=xlCount, TotalList:=Array(12), 
_
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End Sub

Could you help me?

Regards.
César.

Às 19:06 de 07/01/2018, César Calvo escreveu:
> Hello friends.
> I am trying to do Subtotals and for this code:
> 
> oExcel:Selection:Subtotal(2,xlSum,Array(11),True,False,True)
> 
> The aplications gives an error of wrong data type.
> 
> If I do this in the excel file it works fine:
> 
> Sub Macro1()
> '
> ' Macro1 Macro
> '
> 
> '
>     Selection.Subtotal GroupBy:=2, Function:=xlCount, 
> TotalList:=Array(12), _
>         Replace:=True, PageBreaks:=False, SummaryBelowData:=True
> End Sub
> 
> Could you help me?
> 
> Regards.
> César.

Hello.
Those parameters are not in the correct order of the parameters of the 
function.
You cannot use then like in the macro.
Refer to documentation of the SubTotal function in excel.

Just as an example the type of calculation is the first parameter, so 
should be oExcel:Selection:Subtotal( xlSum, .... )

Thanks Joao.


For Sum I am trying this:

aSub := {11}

oExcel:Selection:Subtotal(9,aSub,.T.,.F.,.T.)


And this is the error:

------------------------------------------------------------------------------
ERROR LOG of "D:\CCC_PROGRAMACION\APP_SDI_LAHIGUERA\SINFOGECO.EXE" Date: 
09/01/2018 14:49:48

Xbase++ version     : Xbase++ (R) Version 2.00.875
Operating system    : Windows 10 1709 Build 16299
------------------------------------------------------------------------------
oError:args         :
          -> VALTYPE: C VALUE: Subtotal
          -> VALTYPE: N VALUE: 9
          -> VALTYPE: A VALUE: {11}
          -> VALTYPE: L VALUE: .T.
          -> VALTYPE: L VALUE: .F.
          -> VALTYPE: L VALUE: .T.
oError:canDefault   : Y
oError:canRetry     : N
oError:canSubstitute: Y
oError:cargo        : NIL
oError:description  : Los tipos no coinciden.
Parameter has a wrong data type
oError:filename     : NIL
oError:genCode      : NIL
oError:operation    : Subtotal
oError:osCode       :          -2147352571
oError:severity     :          2
oError:subCode      :       6500
oError:subSystem    : Automation
oError:thread       :          1
oError:tries        : NIL
------------------------------------------------------------------------------
CALLSTACK:
------------------------------------------------------------------------------

Called from INFOTESO:AJUCOB2(536)
Called from INFOTESO:AJUCOB1(206)
Called from (B)INFOTESO:INIT(67)
Called from DSPUSHBUTTON:HANDLEEVENT(1347)
Called from APPEXEC(239)
Called from MAIN(54)


Could you show me the correct code?

Best regards.
César Calvo.


"Joao Grenhas" escribió en el mensaje de 
noticias:12cc32bb$266f279$36f4a8@news.alaska-software.com...

Às 19:06 de 07/01/2018, César Calvo escreveu:
> Hello friends.
> I am trying to do Subtotals and for this code:
>
> oExcel:Selection:Subtotal(2,xlSum,Array(11),True,False,True)
>
> The aplications gives an error of wrong data type.
>
> If I do this in the excel file it works fine:
>
> Sub Macro1()
> '
> ' Macro1 Macro
> '
>
> '
>     Selection.Subtotal GroupBy:=2, Function:=xlCount, 
> TotalList:=Array(12), _
>         Replace:=True, PageBreaks:=False, SummaryBelowData:=True
> End Sub
>
> Could you help me?
>
> Regards.
> César.

Hello.
Those parameters are not in the correct order of the parameters of the
function.
You cannot use then like in the macro.
Refer to documentation of the SubTotal function in excel.

Just as an example the type of calculation is the first parameter, so
should be oExcel:Selection:Subtotal( xlSum, .... )

hi,

> oExcel:Selection:Subtotal(2,xlSum,Array(11),True,False,True)

instead of Array(11) try Excel syntax A1:A10

Thanks Jim but the issue is the same.
I hope support of Alaska could help me because is very strange.
I have proved a lot of posibilities and always appears the error wrong  type 
of data.
Regards.

"Jim Lee" escribió en el mensaje de 
noticias:69592e95$337a4ef4$3848e3@news.alaska-software.com...

hi,

> oExcel:Selection:Subtotal(2,xlSum,Array(11),True,False,True)

instead of Array(11) try Excel syntax A1:A10

hi,

>Array(11),

is this a empty Array ?
have you try to fill it with A1 ... A10 ?

you also can try ActiveX o:callMethod() with VTType() / VT_ARRAY ... search 
in Newsgroup

Jim, I attachment a simple example.
I have done several changes and not work.
Could you view this?
Thanks.

"Jim Lee" escribió en el mensaje de 
noticias:4170931c$ad0838c$3840fe@news.alaska-software.com...

hi,

>Array(11),

is this a empty Array ?
have you try to fill it with A1 ... A10 ?

you also can try ActiveX o:callMethod() with VTType() / VT_ARRAY ... search
in Newsgroup

hi,

you have to start macro just after load file before any action !

as you use SELECTION

   oExcel:Selection:Subtotal()

you have to "select" Elements e.g. using RANGE and activate it

    Range("K2:K77").Select
    Range("K77").Activate

or use oExcel:Subtotal() after ACTIVATE Workbook / SELECT Sheet

Question : why do you want SubTotal afterwards ?

Hi Jim.
I have TMP.xlsx and I want do TMP_Subtotal.xlsx with 
oExcel:Selection:Subtotal(9,xlSum,{11},.T.,.F.,.T.) but always the error.
Could you say how with this macro?
Thanks for your help.
César.

"Jim Lee" escribió en el mensaje de 
noticias:1c57b354$2858f211$16bb@news.alaska-software.com...

hi,

you have to start macro just after load file before any action !

as you use SELECTION

   oExcel:Selection:Subtotal()

you have to "select" Elements e.g. using RANGE and activate it

    Range("K2:K77").Select
    Range("K77").Activate

or use oExcel:Subtotal() after ACTIVATE Workbook / SELECT Sheet

Question : why do you want SubTotal afterwards ?

you still use same Code ?
as i say Array contain RANGE so you MUST activate Workbook and SELECT on 
Sheet what you want.

Finally I have got it.

It is neccesary keep in mind this:
oExcel:ActiveCell:CurrentRegion:Select
It must be first and two considerations:
The range and the number of column where I want get the subtotal.
I send TMP1.xlsx before this macro and TMP2.xlsx after the function.

Many thanks dear Jim.


//--------------------------------------------------------------------------------------------------//

#include "activex.ch"
#include "excel.ch"


PROCEDURE main
LOCAL cFil, oExcel


cFil := "D:\CCC_PROGRAMACION\EXCEL_CHECK\TMP.xlsx"

oExcel = CreateObject( "Excel.Application" )
oExcel:workbooks:open(cFil)
oExcel:Visible := .F.
oExcel:DisplayAlerts := .F.

oExcel:Worksheets("Hoja1"):Activate

oExcel:Range("A:K"):Select

oExcel:Selection:RemoveSubtotal

oExcel:ActiveCell:CurrentRegion:Select

oExcel:Range("A2:K76"):Select

oExcel:Selection:Subtotal(2,xlSum,,,,)

oExcel:Cells(1,1):Select

oExcel:DisplayAlerts := .T.
oExcel:ActiveWorkbook:Save()
oExcel:ActiveWorkbook:Close()
oExcel:Quit()
oExcel:Destroy()

WAIT

RETURN

//--------------------------------------------------------------------------------------------------//

"Jim Lee" escribió en el mensaje de 
noticias:1c57b354$2858f211$16bb@news.alaska-software.com...

hi,

you have to start macro just after load file before any action !

as you use SELECTION

   oExcel:Selection:Subtotal()

you have to "select" Elements e.g. using RANGE and activate it

    Range("K2:K77").Select
    Range("K77").Activate

or use oExcel:Subtotal() after ACTIVATE Workbook / SELECT Sheet

Question : why do you want SubTotal afterwards ?