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

[nooneclose]

I need to perform a subtotal in excel and I would like to automate this process using Autoit if possible like always any and all help will be greatly appreciated. 

I can not find a good example but the two from Microsoft. Here is one of the two from msdn.microsoft.com/en-us/vba/excel-vba/articles/range-subtotal-method-excel

Quote

Worksheets("Sheet1").Activate Selection.Subtotal GroupBy:=1, Function:=xlSum, _ TotalList:=Array(2, 3)

I do not really understand how to translate this into AutoIt, but I gave it a try and here is what I have.

$OpenRange      = "A1:E200"
$xlSum          = -4157
$Added_Array[2] = [2, 3]
$OpenRange.Subtotal("B1", $xlSum, $Added_Array, True, False, True)

I just need to perform a subtotal on a range based on a header called department, and then perform a sum on the results.

Edited November 1, 2018 by nooneclose

[JLogan3o13]

@nooneclose are you looking to enter the subtotal into the workbook, or just gather it for use elsewhere? If you are looking just to get the total, you could do something like this:

#include <Excel.au3>

Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oExcel, @DesktopDir & "\Test.xlsx")
Local $oRange = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.Usedrange.Columns("B:B"))
Local $iTotal = 0

_Excel_BookClose($oWorkbook)
_Excel_Close($oExcel)

    For $a = 1 To UBound($oRange) - 1
        $iTotal += $oRange[$a]
    Next

    ConsoleWrite($iTotal & @CRLF)

If you need to write that subtotal into a cell in the spreadsheet you can use _Excel_RangeWrite to do so. Also, in the future, posting an example of the text file or spreadsheet you're working on helps immensely, so we don't have to recreate them

[water]

As AutoIt does not support parameters by name you need to specify them in the sequence as documented here:
https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-subtotal-method-excel

$OpenRange      = "A1:E200"
$xlSum          = -4157
$Added_Array[2] = [2, 3]
$oExcel.Worksheets("Sheet1").Range($OpenRange).Subtotal("B1", $xlSum, $Added_Array, True, False, True)

BTW: I'm not sure "B1" is correct. I think it should be 2 (according to the docu I mentiond above). Can't test at the moment so everything I posted might be wrong

[nooneclose]

Thank you for your replies, but I sadly cannot test or use these until I can figure out how to delete certain names in a column first. my Program will not work as intended if I don't. 

[nooneclose]

I was able to Jerry-rig a way to find the cell locations and delete them though I do not like having to manually enter in all names that should be deleted I could not think of another way to do it. Here is how I did it: 

Local $NameToDelete1  = _Excel_RangeFind($OpenWorkbook, "Smith, Bill")
Local $NameCell       = $NameToDelete1[0][2]
Local $CellNumber     = StringSplit($NameCell, "A, B, C, D, E")
Local $CellRange      = $NameCell & ":E" & $CellNumber[2]
_Excel_RangeDelete($OpenWorkbook.ActiveSheet, $CellRange, $xlShiftUp)

I can now focus on the subtotal.  

[nooneclose]

@JLogan3o13 I am trying to perform a subtotal on every used row in columns A-I and every change in the header at B1. I also want this subtotal to perform the "Sum" function. 

Basically, this subtotal should take my roster (A1-I200, row 1 is the Headers so I must include them) break it down by the employee sub-departments and then add up how many are in each department.

I can manually do this in excel but I REALLY want to automate these last few processes.    

[water]

But what should get written to the Excel sheet? The formula (so it changes whenever you modify the sheet) or just the result as a numeric value?

[nooneclose]

@waterThe results of the subtotal should be written to the excel sheet. I'll show you want I'm trying to automate. I changed the names on purpose. 

EMP. TYPE	SUB-DEPT	NAME	HOURS AS TIME	HOURS AS GEN.
Staff	Warehouse	Smith, Bill	Smith, Bill	40:00
Staff	HVAC	Smith, Bill	Smith, Bill	42:45:00
Staff	General Maint.	Smith, Bill	Smith, Bill	50:30:00
Staff	Construction	Smith, Bill	Smith, Bill	64:30:00
Staff	Plumber	Smith, Bill	Smith, Bill	25:00

 I need to go from the above example to the below example. A subtotal based on the sub-debt which is Colum B1

EMP. TYPE	SUB-DEPT	NAME	HOURS AS TIME	HOURS AS GEN.
	Appliance Total			103.25
	Construction Total			541
	Electrician Total			320.5
	General Maint. Total			591.25
	HVAC Total			377

Edited August 15, 2018 by nooneclose

[water]

I'm on vacation right now. Will test, when I return

[nooneclose]

@water Thank you, and enjoy your vacation! Everyone needs a break every now and again. Sometimes I wish it was again and again and again though.  

[water]

Can you please post a real life example so we can check that our calculation is correct?

Example: You mention SUB-DEPT "Appliance Total" in your result matrix but it isn't mentioned in the source matrix.

[nooneclose]

I think I understand what you're asking for.  @water

Here is what I get from a fresh report and I have to do a bunch of stuff and a subtotal to make it look like the below image. 

I do some inserts and stuff and then I perform some sorts then I do a subtotal. I have mouse clicks doing the subtotal now but I want to automate that process. 

to see how I perform the subtotal see the image below.  

Did these images answer your question? I do an FTE report. 

[JLogan3o13]

@nooneclose it would help immensely if you post an actual file, rather than pictures (redacting your company data of course). Otherwise we have to spend a lot of time recreating your spreadsheet.

[junkew]

In general I would say insert a pivot table on a new sheet which directly calculates all the summing you want.

Any reason to do it from AutoIt and not straight from VBA itself?

 

 

[AndyG]

8 hours ago, junkew said:

Any reason to do it from AutoIt and not straight from VBA itself?

...should be the question of 90% of all the Excel-relating requests in this forum. 

 

*OT-mode ON*

The only (for me) meaningful use of AutoIt with Excel is to extract or to put in/out Data from/to extern (3rd) Programs. Or control processes of other Programs from Excel/Word/any other VBA controllable software.

I cannot understand why people use a programming language like AutoIt (with whom they have no experience, otherwise they would not ask the simplest tasks here in the forum) instead of using the also "BASIC-like" build in programming language. Don´t they know what the "B "in VBA means?! 

I think it would be a good idea to show how easy most of the here posted "Excel-related problems" could be solved with some lines of VBA-code. Some links to a friendly VBA forum anyone?  Could be the start of a cooperation of two forums with mutual participation. (BIG smilie here ^^)

I bet that even experienced VBA-programmers would like AutoIt because of its wonderful possibilities!

*OT-mode OFF*

Edited August 26, 2018 by AndyG

[junkew]

Anyway it would be something close to

$Added_Array[5] = [5,6,7,8,9] 'Totals added to these columns
'vba worksheets(1).Cells().Subtotal GroupBy:=2, Function:=$xlSum, TotalList:=Array(5,6,7,8,9), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
$oWorkbook.worksheets(1).Cells().Subtotal 2, $xlSum, $added_Array, True, False, True

Advice: First record a vba macro and attach to your question it will be easier to translate

Deleting you can do more efficient by applying delete on a filteredrange

https://danwagner.co/how-to-delete-rows-with-range-autofilter/

[nooneclose]

@AndyG if it is so easy then why don't you show me? There is no reason or need to be a puffed up nerd here. I came for help. And Yes, I do not know much about this language but I also did not know about VBA trust me if I knew an easier way I would have done it that way. 

Here is the file you asked for you @JLogan3o13 

Data.xlsx

Edited August 30, 2018 by nooneclose

[junkew]

Based on your last answer vba is much easier to solve your problem.

1. Alt F11 and record macro are your friends
2. Start record
3. Do your things
4. Stop record
5. Check your vba code and modify
6. Save as xlsm file

In my previous post you see the vba one liner commented out to add your subtotals

[junkew]

I downloaded your xlsx and its really a one liner recorded in VBA. Watch some instructions on youtube to get more instructed on vba and macro's

My advice is allways to record a VBA macro before transforming to another language like AutoIt

Details recorded

1. menu view, macros, record macro
2. menu data, subtotals, follow wizard
3. menu view, macros, stop macro
4. menu view, macros, edit macro to change 

Recorded

Sub Macro1()
'
' Macro1 Macro
'

'
    Cells.Select
    Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(4, 5), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End Sub

Changed to

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

based on post 3 you should be able to transform to Autoit

 

[nooneclose]

Thank you very much! @junkew I will try that next week and let you know how it performs.