Jump to content
Sign in to follow this  
erik7426

is this possible - excel insert totals

Recommended Posts

erik7426

I have an excel file that contains an account number and a dollar amount. Each day the file will have a different number of rows. Is it possible to have AutoIt open the excel file, determine the last row, and insert a count on the account number field and a sum on the dollar amount field?

Share this post


Link to post
Share on other sites
JLogan3o13

It is possible. If you want to write on the last row with data in it, you could do this:

Const $xlCellTypeLastCell = 11

$oExcel = ObjCreate("Excel.Application")
    $oBook = $oExcel.Workbooks.Open("C:\Users\JeremiaL\Desktop\Test.xls")
    $oExcel.Visible = True
    $oSheet = $oBook.Worksheets(1)
    $oSheet.Activate

    $oRange = $oSheet.UsedRange
    $oRange.SpecialCells($xlCellTypeLastCell).Activate

If you want the first open row, add this to the end:

$newRow = $oExcel.ActiveCell.Row + 1
    $oExcel.Range("A" & $newRow).Activate

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

How to get your question answered on this forum!

Share this post


Link to post
Share on other sites
erik7426

 

It is possible. If you want to write on the last row with data in it, you could do this:

Const $xlCellTypeLastCell = 11

$oExcel = ObjCreate("Excel.Application")
    $oBook = $oExcel.Workbooks.Open("C:\Users\JeremiaL\Desktop\Test.xls")
    $oExcel.Visible = True
    $oSheet = $oBook.Worksheets(1)
    $oSheet.Activate

    $oRange = $oSheet.UsedRange
    $oRange.SpecialCells($xlCellTypeLastCell).Activate

If you want the first open row, add this to the end:

$newRow = $oExcel.ActiveCell.Row + 1
    $oExcel.Range("A" & $newRow).Activate

 

Thanks, that gets me part of the way there. Any idea how to get it to insert a 'sum' and a 'count'?

Share this post


Link to post
Share on other sites
JLogan3o13

So you want a sum of all dollar amounts? How about a screenshot of an excel example, so we can see how you have/want the data laid out? Then it should be relatively easy to get you where you want to be.


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

How to get your question answered on this forum!

Share this post


Link to post
Share on other sites
erik7426

So you want a sum of all dollar amounts? How about a screenshot of an excel example, so we can see how you have/want the data laid out? Then it should be relatively easy to get you where you want to be.

 

screenshot attached...

post-13077-0-14370400-1394487931_thumb.j

Edited by erik7426

Share this post


Link to post
Share on other sites
JLogan3o13

Ok, I'm not sure what the purpose of the count numbers are, or how you're totaling them, but for a subtotal you could do something like this:

 

Beginning with this:

post-54985-0-09157800-1394494503_thumb.p

 

You could do something like this:

#include <Excel.au3>
 
Local $subTotal, $grandTotal
$oExcel = _ExcelBookOpen(@DesktopDir & "\Test.xls")
$oRange = $oExcel.Worksheets(1).UsedRange
$oRange.SpecialCells($xlCellTypeLastCell).Activate
$oRow = $oExcel.ActiveCell.Row
$subTotalArray = _ExcelReadArray($oExcel, 2, 3, $oRow, 1)
 
For $element in $subTotalArray
   $subTotal += $element
Next
 
$grandTotal = _ExcelWriteCell($oExcel, $subTotal, $oRow + 1, 3)

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

How to get your question answered on this forum!

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
Sign in to follow this  

×