Jump to content
nooneclose

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

Recommended Posts

nooneclose
Posted (edited)

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 by nooneclose

Share this post


Link to post
Share on other sites
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 :)


√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites
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 ;)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
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. 

Share this post


Link to post
Share on other sites
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. :) 

Share this post


Link to post
Share on other sites
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.    

Share this post


Link to post
Share on other sites
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?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
nooneclose
Posted (edited)

@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 by nooneclose

Share this post


Link to post
Share on other sites
water

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
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. :) 

Share this post


Link to post
Share on other sites
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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
nooneclose

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

5b8054f765811_Screenshot(60)_LI.thumb.jpg.ed41f07ff42c8a75747e7230746913d1.jpg

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. 

5b805536be09a_Screenshot(58).thumb.png.2ef6669983851b11aa4c914a10d8edce.png

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.  

5b805677e65f4_Screenshot(61)_LI.jpg.1d3c3fa2ab871df1038de1cebdc3a9f3.jpg

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

Share this post


Link to post
Share on other sites
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.

  • Thanks 1

√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites
AndyG
Posted (edited)
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 by AndyG
  • Like 1

Share this post


Link to post
Share on other sites
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/

Share this post


Link to post
Share on other sites
nooneclose
Posted (edited)

@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 by nooneclose

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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

 

Share this post


Link to post
Share on other sites
nooneclose

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

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Similar Content

    • nooneclose
      By nooneclose
      Good morning everyone, I thought I had already solved this issue but it turns out I did not. 
      My code finds unread emails with this specific subject line of "request" but when I change the subject to SKIPPED + "request" = ("SKIPPED request") the program still finds the email and tries to process it. I only want to process emails with the exact match subject of "request".
      Here is my code that "finds" the unread emails with the subject of "request" or so I thought. 
      Func ListUnreadEmails() ;******************************************************************************* ; Lists all unread E-mails from the folder Outlook-UDF-Test ;******************************************************************************* ; Stores all the unRead emails into an array Global $aItems = _OL_ItemFind($oOutlook, "*\Outlook-UDF-Test", $olMail, _ "[UnRead]=True", "Subject", "request", "EntryID,Subject", "", 1) ; Displays the array of unRead emails If IsArray($aItems) Then ;_ArrayDisplay($aItems, "OutlookEX UDF: _OL_ItemFind - Unread mails") Else MsgBox(48, "OutlookEX UDF: _OL_ItemFind Example Script", _ "Could not find an unread mail. @error = " & @error & ", @extended: " & @extended) EndIf ; Gets the number of unread emails Global $numberOfUnRead = UBound($aItems, $UBOUND_ROWS) - 1 ;MsgBox("", "Number of Unread emails", $numberOfUnRead) EndFunc It acts as if any part of the subject containing the word "request" and the email is unread that it will try to process it. (I think)
    • KhalidAnsari
      By KhalidAnsari
      Hi,
      I need to automate Java application using AutoIt, I have refer forum for the same, as per forum I am using Java Access Bridge to finding control name. But could not set value to control. Below is my sample code 
      Send("#r")
      sleep(1500)
      Send("D:\javaapplication\javaapplication\Hello.jar{Enter}")
      WinActivate("Penjumlahan")
      sleep(1500)
      ControlSetText("Penjumlahan", "", "Name:  A :", "demo")
      Java Application and Java Ferret screen

    • QaisBsharat
      By QaisBsharat
      Hello, 
      May i know how can i automation qt application?
      Using controlclick not mouse and positions
       
      Thanks
    • nooneclose
      By nooneclose
      Good afternoon, I am in need of some help. I am sure this is a stupid question requiring only one or two lines of code. However, I would greatly appreciate the help I cannot figure this out. I also tried searching for the answer on the internet but no one except me apparently seems to be having a hard time figuring this out and or is asking about it. 
      I simply want to change the "status" of an email from unread to read once I have processed it. My code is over 500 lines and I would like not to clutter this post with it. Assume I have all my includes and connections properly defined and stuff. 
      Here is the bit of code where I am trying to change the email that was used from unread to read:
      Func ChangeEmailStatus() ;******************************************************************************* ; changes the status of an email from unread to Read ;******************************************************************************* Local $iRows = UBound($aItems, $UBOUND_ROWS) MsgBox("", "Number of Unread emails (Before Change)", $iRows) _OL_ItemModify($oOutlook,$aItems[$i][0], Default, "Read=True") MsgBox("", "Array Display 1", $aItems[1][0]) MsgBox("", "Array Display 2", $aItems[2][0]) Local $iRows = UBound($aItems, $UBOUND_ROWS) MsgBox("", "Number of Unread emails (After Change)", $iRows) EndFunc  
    • SharpDressedMan
      By SharpDressedMan
      Hello,
      Is there any way to check for validity of a pointer to a DllStruct ?
      In the above code, a DllStruct is created from an invalid pointer. How to prevent from doing this ?
      local $tStruct = DllStructCreate("int i") DllStructSetData($tStruct, "i", 123) local $pStruct = DllStructGetPtr($tStruct) $tStruct = 0 ; destroy DllStruct ==> $pStruct becomes invalid ; how to check here for validity of $pStruct and prevent from doing the following ? $tStruct = DllStructCreate("int i", $pStruct) ; create DllStruct from invalid pointer... MsgBox(0, @ScriptName, @error) ; displays '0' : invalid pointer is not detected by DllStructCreate() MsgBox(0, @ScriptName, IsDllStruct($tStruct)) ; displays '1' : tStruct is assumed as a valid DllStruct object, which is not Thanks for help
×