Excel Subtotal Function

[MLM]

I am new to AutoIt and am trying to call the Excel Subtotal function on a range of cells. Here is the output from the Excel macro:

Range("B3:P30").Select

Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(7, 8, 9, 10 _

, 11, 12, 13, 14, 15), Replace:=True, PageBreaks:=False, SummaryBelowData:= _

True

How do I convert this to the AutoIt command format?

Thanks in advance for any and all help.

[big_daddy]

Subtotal is actually a method of the Range object.

#include <Word.au3>
#include <Excel.au3>

Const $xlSum = -4157

Global $sFilePath = "C:\Temp\Book1.xlsx"
Global $aArray[9] = [7, 8, 9, 10, 11, 12, 13, 14, 15]

_WordErrorHandlerRegister()
$oExcel = _ExcelBookOpen($sFilePath)
$oRange = $oExcel.Activesheet.Range("B3:P30")
$oRange.Subtotal(1, $xlSum, $aArray, True, False, True)

[MLM]

Subtotal is actually a method of the Range object.

#include <Word.au3>
#include <Excel.au3>

Const $xlSum = -4157

Global $sFilePath = "C:\Temp\Book1.xlsx"
Global $aArray[9] = [7, 8, 9, 10, 11, 12, 13, 14, 15]

_WordErrorHandlerRegister()
$oExcel = _ExcelBookOpen($sFilePath)
$oRange = $oExcel.Activesheet.Range("B3:P30")
$oRange.Subtotal(1, $xlSum, $aArray, True, False, True)

Thanks for your response; however, my program still isn't working. Here is the error I am receiving:

>Running:(3.3.0.0):C:\Program Files\AutoIt3\autoit3.exe "C:\Vital Signs\May 2009\reports_051809\Format Step #1.au3"

C:\Vital Signs\May 2009\reports_051809\Format Step #1.au3 (204) : ==> Variable must be of type "Object".:

$oRange.Subtotal(1, $xlSum, $aArray, True, False, True)

$oRange^ ERROR

->13:18:14 AutoIT3.exe ended.rc:1

Here are my include statements:

#include <C:\Program Files\AutoIt3\Include\ExcelCOM_UDF.au3> ; using this for the _ExcelCellMerge function.

#include <c:\Program Files\AutoIt3\Include\Word.au3> ; not using any word functions; however, I included it anyway

Here is the pertinent code:

Const $xlSum = -4157

Global $aArray[9] = [7, 8, 9, 10, 11, 12, 13, 14, 15]

$oRange = $oExcel.Activesheet.Range("B3:P30").Select

$oRange.Subtotal(1, $xlSum, $aArray, True, False, True)

[evilertoaster]

Why'd you take out

$oExcel = _ExcelBookOpen($sFilePath)

?

[MLM]

Thanks for your response; however, my program still isn't working. Here is the error I am receiving:

>Running:(3.3.0.0):C:\Program Files\AutoIt3\autoit3.exe "C:\Vital Signs\May 2009\reports_051809\Format Step #1.au3"

C:\Vital Signs\May 2009\reports_051809\Format Step #1.au3 (204) : ==> Variable must be of type "Object".:

$oRange.Subtotal(1, $xlSum, $aArray, True, False, True)

$oRange^ ERROR

->13:18:14 AutoIT3.exe ended.rc:1

Here are my include statements:

#include <C:\Program Files\AutoIt3\Include\ExcelCOM_UDF.au3> ; using this for the _ExcelCellMerge function.

#include <c:\Program Files\AutoIt3\Include\Word.au3> ; not using any word functions; however, I included it anyway

Here is the pertinent code:

Const $xlSum = -4157

Global $aArray[9] = [7, 8, 9, 10, 11, 12, 13, 14, 15]

$oRange = $oExcel.Activesheet.Range("B3:P30").Select

$oRange.Subtotal(1, $xlSum, $aArray, True, False, True)

I ran your code as a separate file and it worked. That being proven, I prior to calling the section starting with Const $xlSum = -4157, I saved and closed Excel as follows:

_ExcelBookSave($oExcel)

_ExcelBookClose($oExcel, 1, 0) ; Close fileName.xls

$oExcel = _ExcelBookOpen(($dirPath & $file & $ext), 0) ; Open the fileName.xls

Const $xlSum = -4157

Global $aArray[9] = [7, 8, 9, 10, 11, 12, 13, 14, 15]

$oRange = $oExcel.Activesheet.Range("B3:P30").Select

$oRange.Subtotal(1, $xlSum, $aArray, True, False, True)

and still receive the 'Variable must be of type "Object".' error.

[evilertoaster]

Does .Activesheet work when you open the workbook as not visible?

Maybe try

$oExcel = _ExcelBookOpen($dirPath & $file & $ext); Open the fileName.xls

real quick instead of what you have and verify...

[MLM]

Does .Activesheet work when you open the workbook as not visible?

Maybe try

$oExcel = _ExcelBookOpen($dirPath & $file & $ext); Open the fileName.xls

real quick instead of what you have and verify...

That doesn't work either.

[evilertoaster]

So,

#include <Word.au3>
#include <Excel.au3>
Const $xlSum = -4157
Global $sFilePath = "C:\Temp\Book1.xlsx"
Global $aArray[9] = [7, 8, 9, 10, 11, 12, 13, 14, 15]
_WordErrorHandlerRegister()
$oExcel = _ExcelBookOpen($sFilePath)
$oRange = $oExcel.Activesheet.Range("B3:P30")
$oRange.Subtotal(1, $xlSum, $aArray, True, False, True)oÝ÷ Ø:³
+­jëh×6#include <C:\Program Files\AutoIt3\Include\ExcelCOM_UDF.au3> ; using this for the _ExcelCellMerge function. 
#include <c:\Program Files\AutoIt3\Include\Word.au3> ; not using any word functions; however, I included it anyway
_ExcelBookSave($oExcel)
_ExcelBookClose($oExcel, 1, 0) ; Close fileName.xls
$oExcel = _ExcelBookOpen($dirPath & $file & $ext) ; Open the fileName.xls
Const $xlSum = -4157
Global $aArray[9] = [7, 8, 9, 10, 11, 12, 13, 14, 15]
$oRange = $oExcel.Activesheet.Range("B3:P30").Select
$oRange.Subtotal(1, $xlSum, $aArray, True, False, True)

Does not...humm.

Well I'm trying think of a list of problematic candidates...

All I can think of is either the path for the file being wrong or $oExcel being tainted by previous use or an outside function... There isn't much difference between the the code blocks.

[big_daddy]

You need to stop using ExcelCOM_UDF.au3 and use the included Excel.au3

You also need to add some error checking after calling _ExcelBookOpen

Edited May 28, 2009 by big_daddy

[MLM]

You need to stop using ExcelCOM_UDF.au3 and use the included Excel.au3

You also need to add some error checking after calling _ExcelBookOpen

OK. Thanks. I will give that a try.