[SOLVED] How to perform a Subtotal in Excel using Autoit

[nooneclose]

@junkew Here is what I tried. This line of code is full of errors though. (Syntax and statement cannot be just an expression)

Worksheets(1).Cells.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(7), Replace:=True, PageBreaks:=False, SummaryBelowData:=True

I used VBA to record this like you said and showed me. I just do not know how to properly transform this into the Autoit language.

Any hints or tips?

[water]

AutoIt does not support named parameters.
MSDN explains how positional parameters need to be specified: https://docs.microsoft.com/de-de/office/vba/api/excel.range.subtotal

Untested:

Global $aTotalList[1] = [7]
$oWorkbook.Worksheets(1).Cells.Subtotal(2, $xlSum, $aTotalList, True, False, True)

[nooneclose]

@water

Here is what I have/had as of 1 and a half hours ago.

Const $xlSum = -4157
Global $sub_Array[7] = [1, 2, 3, 4, 5, 6, 7]
$oRange = $OpenWorkbook.Activesheet.Range("A1:N" & $IndexRows)
$oRange.Subtotal(2, $xlSum, $sub_Array, True, False, True)

I do not understand what the

$xlSum = -4157

means I just know it is needed.

This pretty much works but I have to collapse it manually once the subtotal runs. The only issue I am having is that I need it to "add the subtotal to" Row 1 Col 6 and I think it's adding it to Row 1 Col 5.

any tips would be great. 

[water]

xlsum defines that the subtotal shoudl sum up the values. You could calculate average, product ... as described here: https://docs.microsoft.com/en-us/office/vba/api/excel.xlconsolidationfunction

[nooneclose]

@water Thank you very much. I got the code to work perfectly now the only thing I need is to transform this VBA code into Autoit code so it can collapse the subtotal for me. 

 

 ActiveSheet.Outline.ShowLevels RowLevels:=2

Where do I go to find out how to properly edit this so it will work in Autoit? 

[junkew]

$openworkbook.outline.showlevels 2

Should do the trick

1. Named parameter := .... is not supported in autoit so leave name of parameter away.
2. Sometimes the parameter is the nth parameter and you have to use n minus one , to get it working

 

[nooneclose]

@junkew I made a new post just for this question at: