Subtotals in Excel object model

J

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
 
T

Tom Ogilvy

To the best of my knowledge, it is exposed.

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

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
 
T

Tom Ogilvy

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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Calculating subtotals 0
Calculating subtotals 22
Subtotals in object model? 2
subtotals 1
Excel Subtotals 2
Unmatched rows 0
Excel 2007 - Outline not displaying all nested subtotals 0
nested Subtotals in Excel 2007 1

Top