Excel 2003 Subtotal order and grouping incorrect in SP1

[Adam Platt]

I'm using Microsoft Visual Foxpro 8.0 to generate an excel-based report which
uses the subtotal function. After installing Excel2003 SP1, the subtotals no
longer order or group correctly.
Where the total rows should read something like:

Total 2
Total 1
Grand Total

They instead read like:

Total 1
Total 2
Grand Total

Also, the data is not grouped by Total 2. Where on the left hand side there
should be a tree-type view where data can be collapsed/expanded, The total
line for Total 2 is not groupable.

Many other people have had this problem, and there have been two definitive
fixes pointed to:
1. Install the following hotfix
http://support.microsoft.com/kb/833855

2. Add a DWORD value of 1 called Excel97Subtotals into the registry key
HKEY_CURRENT_USER\software\microsoft\office\11\excel\options

I have tried both, and only had success with editing the registry.
Unfortunately, I need to use a fix that can be mass distributed and applied
by users that have very little computer knowledge. Has anyone else tried the
Microsoft hotfix for this issue and still had a problem? Are there any known
issues about this hotfix?

 

[Jim Rech]

I encounter the problem for the first time about a month ago and the answer
was the registry entry you cite. One way or the other you have to get that
entry made on the users' machines. Here's code that will do it:

Sub RegWriteXL97Subtotals()
Dim wsh As Object
Set wsh = CreateObject("WScript.Shell")
wsh.RegWrite
"HKCU\Software\Microsoft\Office\11.0\Excel\Options\Excel97Subtotals", 1,
"REG_DWORD"
End Sub

--
Jim Rech
Excel MVP
| I'm using Microsoft Visual Foxpro 8.0 to generate an excel-based report
which
| uses the subtotal function. After installing Excel2003 SP1, the subtotals
no
| longer order or group correctly.
| Where the total rows should read something like:
|
| Total 2
| Total 1
| Grand Total
|
| They instead read like:
|
| Total 1
| Total 2
| Grand Total
|
| Also, the data is not grouped by Total 2. Where on the left hand side
there
| should be a tree-type view where data can be collapsed/expanded, The total
| line for Total 2 is not groupable.
|
| Many other people have had this problem, and there have been two
definitive
| fixes pointed to:
| 1. Install the following hotfix
| http://support.microsoft.com/kb/833855
|
| 2. Add a DWORD value of 1 called Excel97Subtotals into the registry key
| HKEY_CURRENT_USER\software\microsoft\office\11\excel\options
|
| I have tried both, and only had success with editing the registry.
| Unfortunately, I need to use a fix that can be mass distributed and
applied
| by users that have very little computer knowledge. Has anyone else tried
the
| Microsoft hotfix for this issue and still had a problem? Are there any
known
| issues about this hotfix?

 

[Matt]

Does anyone know if there will be another Excel 2003 hotfix or Service
Pack in the future that will resolve the Subtotaling problems?

Anyone who studies this issue carefully, and experiments, will realize
that even after all the hotfixes, service packs, registry hacks, etc.,
Subtotaling still does not work correctly in all cases.

A prime example is when your data has "breaks" (groups) that contain
only one row.