Subtotals in Excel object model

[Josh Sale]

I have a worksheet with a data list that uses Subtotals
which I programatically create but that users sometimes
modify (e.g., they change page break before or display
totals above instead of below).

Under certain circumstances I need to remove the
Subtotals, do some processing and then put back the
Subtotals.

I've searched around in the Excel object model looking for
the representation of an active Subtotal but not found
anything. Is the definition of an existing Subtotal
available to VBA? If so, I'll save away all of the
current settings, remove the existing subtotals, do my
processing and then reinstate the Subtotals as they were
before I removed them.

TIA,

josh

 

[Tom Ogilvy]

To the best of my knowledge, it is exposed.

Copy the sheet, do your work and then delete the copy.

 

[Tom Ogilvy]

Let's try again,
to the best of my knowledge, none of its settings are exposed.

 

[Kaboki]

The way i right most of my vba for excel is to first record what I want
then I edit the macro and get exactly what I want

Range("B5").Select ' needs to be within the data area or assign a
selection name
Selection.Subtotal , Function:=xlSum, TotalList:=Array(4), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Selection.Subtotal , Function:=xlSum, TotalList:=Array(4), _
Replace:=False, PageBreaks:=False, SummaryBelowData:=True
Selection.Subtotal , Function:=xlSum, TotalList:=Array(4), _
Replace:=False, PageBreaks:=False, SummaryBelowData:=True


groupby is like collumn 1 , 2 ,3 etc

XlConsolidationFunction can be one of these XlConsolidationFunction
constants.
xlAverage
xlCount
xlCountNums
xlMax
xlMin
xlProduct
xlStDev
xlStDevP
xlSum
xlUnknown
xlVar
xlVarP

totallist looks like a width of table type of thing

apply this function to one button and then apply this to another

Range("B5").Select

Selection.RemoveSubtotal

to remove it

I hope this helps I hate coding in excel

 

[Tom Ogilvy]

He was asking how to determine the users settings (what options did the user
select) after running subtotal manually.

 

[Josh Sale]

Thanks Tom for confirming my own research.

Seems like a nice possible enhancement to Excel if any MS product folks are
out there.

BTW, I did figure out an extreamly ugly way around my problem: save the
workbook in xml, parse the xml and then delete the xml file. Unfortunately,
even that approach has some problems since the xml file doesn't really save
the meta data associated with the Subtotals. Rather it just saves the
summary formulas. Oh yeah, and it completly omits the outline info.

I won't be pursuing this ugly solution ...

josh