Reference Excel Totals Rows by Column Name?

[jaybro]

Suppose your have a table in Excel with the totals row showing. You know the column headings and would like to apply a formula to a specific total cell using nothing more than the column heading text and table objects.

The "Get_Excel" function (found on the forums) gives me a solid handle on the workbook. And, this code works... it's just... ugly:

Code: Select all

xl := Excel_Get()
t := xl.ActiveSheet.ListObjects(1)
t.ShowTotals := 1

colName := "A Known Column Heading Name"
xl.Range(t.Name . "[[#Totals],[" . colName . "]]").Formula := "=SUBTOTAL(109,[colName])"		; this is the line I'm trying to improve upon

This is working but I can't seem to piece this together a more elegant solution using table objects.

I wish this would work but it doesn't:

Code: Select all

t.ListColums(colName).TotalsRowRange.Formula := "=SUBTOTAL(109,[colName])"

Any ideas? Again, I'm not trying to use the column's index number here... I have a column name in hand and want to apply the formulas based on know column names.

[FanaticGuru]

I wish this would work but it doesn't:

Code: Select all

t.ListColums(colName).TotalsRowRange.Formula := "=SUBTOTAL(109,[colName])"

Any ideas? Again, I'm not trying to use the column's index number here... I have a column name in hand and want to apply the formulas based on know column names.

You don't normally set formulas for the total row in a table. It has predefined formula that you should use. They are what you chose from the dropdown menu when you click on one of these cells. You can set the formulas directly and not use the predefined but then you are kind of working against the grain of tables.

Code: Select all

xlApp := ComObjActive("Excel.Application")
xlTable := xlApp.ActiveSheet.ListObjects("Table1")
xlTable.ShowTotals := 1
xlTable.ListColumns("Amount").TotalsCalculation := 1

;~ xlTotalsCalculationNone  := 0
;~ xlTotalsCalculationSum := 1
;~ xlTotalsCalculationAverage := 2
;~ xlTotalsCalculationCount := 3
;~ xlTotalsCalculationCountNums := 4
;~ xlTotalsCalculationMin := 5
;~ xlTotalsCalculationMax := 6
;~ xlTotalsCalculationStdDev := 7
;~ xlTotalsCalculationVar := 8

I used actual names to identify the table and column but you can also use index numbers.

FG

[jaybro]

Thanks, FG!

I didn't know about TotalsCalculation object.

Here's what I came up for a cleaner range reference (which I'm happy with) to manage the Totals row:

ListColumns("Column1").Total.Address

Code: Select all

xl := Excel_Get()
t := xl.ActiveSheet.ListObjects(1)
t.ShowTotals := 1

xl.Range(t.ListColumns(""Avg WrapUp per Call"").Total.Address).Formula := ""=IFERROR((SUM([[#Totals],[Wrap-Up Time]],[[#Totals],[Wrap-Up (NRC 14)]]) / [[#Totals],[Total Phone Contacts]]),0)"

ExitApp