Sign in to follow this  
Followers 0
MLM

Excel Subtotal Function

10 posts in this topic

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.

Share this post


Link to post
Share on other sites



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)

Share this post


Link to post
Share on other sites

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)

Share this post


Link to post
Share on other sites

Why'd you take out

$oExcel = _ExcelBookOpen($sFilePath)
?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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...

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

#9 ·  Posted (edited)

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

Share this post


Link to post
Share on other sites

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.

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  
Followers 0