Alaska Software Inc. - SubTotal in Excel Files
Alaska Software Inc. - SubTotal in Excel Files
Username: Password:
AuthorTopic: SubTotal in Excel Files
César Calvo SubTotal in Excel Files
on Sun, 07 Jan 2018 20:06:00 +0100
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.


XPPERROR.LOG
Doc1.pdf
Joao GrenhasRe: SubTotal in Excel Files
on Tue, 09 Jan 2018 13:03:03 +0000
À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, .... )
César Calvo Re: SubTotal in Excel Files
on Tue, 09 Jan 2018 14:51:58 +0100
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, .... )
Jim LeeRe: SubTotal in Excel Files
on Wed, 10 Jan 2018 23:41:17 +0100
hi,

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

instead of Array(11) try Excel syntax A1:A10
César Calvo Re: SubTotal in Excel Files
on Thu, 11 Jan 2018 03:33:28 +0100
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
Jim LeeRe: SubTotal in Excel Files
on Thu, 11 Jan 2018 06:27:14 +0100
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
César Calvo Re: SubTotal in Excel Files
on Thu, 11 Jan 2018 07:24:02 +0100
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


EXCEL_CHECK.zip
Jim LeeRe: SubTotal in Excel Files
on Thu, 11 Jan 2018 22:51:46 +0100
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 ?
César Calvo Re: SubTotal in Excel Files
on Fri, 12 Jan 2018 06:59:42 +0100
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 ?


TMP.xlsx
TMP_Subtotal.xlsx
XPPERROR.LOG
Jim LeeRe: SubTotal in Excel Files
on Fri, 12 Jan 2018 22:37:09 +0100
you still use same Code ?
as i say Array contain RANGE so you MUST activate Workbook and SELECT on 
Sheet what you want.
César Calvo Re: SubTotal in Excel Files
on Fri, 12 Jan 2018 23:18:17 +0100
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 ?


TMP1.xlsx
TMP2.xlsx