Apex Code Development You need to sign in to do that
Don't have an account?
D Farns Excel 2003 Hangs up when subtotaling
Not sure if this relates to the Office Edition plug in, but it seems to be.
I've got a Workbook (Excel 2003 SP1) using OE to refresh a couple of SF reports. All is fine until I try to add subtotals to a worksheet. The sheet in question has no calcs, no data ranges, no links/references, it's just 120 rows of data pasted (paste special-values) from another sheet. After selecting range (A1:AB120) I add subtotals using the "Subtotal" option in "Tools" and Excel pegs the processor to 100% for 5-7 full minutes. Same thing when I try to remove the subtotals.
Micrsoft says to change the Calculation method to "Manual" and then add subtotals. This workbook is already set to Manual due to the shear number (400,000+) of formulas on other sheets within the workbook. I control calculating with VBA.
If I paste the same range of data to a new workbook, the subtotals are added in a split second. If I paste the data to a new workbook AND the workbook using OE is open as well, it takes 5-7 at 100% utilization.
Has anyone seen Office Edition cause this type of behavior? Any help would be greatly appreciated. These subtotals are the finishing touch to a 3 month project and are the last thing I expected to give me trouble. Haven't tried adding subtotals from VBA yet, but I expect the same result.
I've got a Workbook (Excel 2003 SP1) using OE to refresh a couple of SF reports. All is fine until I try to add subtotals to a worksheet. The sheet in question has no calcs, no data ranges, no links/references, it's just 120 rows of data pasted (paste special-values) from another sheet. After selecting range (A1:AB120) I add subtotals using the "Subtotal" option in "Tools" and Excel pegs the processor to 100% for 5-7 full minutes. Same thing when I try to remove the subtotals.
Micrsoft says to change the Calculation method to "Manual" and then add subtotals. This workbook is already set to Manual due to the shear number (400,000+) of formulas on other sheets within the workbook. I control calculating with VBA.
If I paste the same range of data to a new workbook, the subtotals are added in a split second. If I paste the data to a new workbook AND the workbook using OE is open as well, it takes 5-7 at 100% utilization.
Has anyone seen Office Edition cause this type of behavior? Any help would be greatly appreciated. These subtotals are the finishing touch to a 3 month project and are the last thing I expected to give me trouble. Haven't tried adding subtotals from VBA yet, but I expect the same result.
Message Edited by D Farns on 02-10-2006 07:18 AM
Message Edited by D Farns on 02-10-2006 07:40 AM
Ron, thanks for the quick response. It looks like Functions are non-volatile by default and the workbook is set to "manual" calculations. That's the only setting I'm aware of that effects the volatility of a book/sheet. If there's another setting (menu or VBA driven) that you're aware, please point me in the right direction.
I've observed that even with Calc set to Manual, new formula's entered in a cell are still calculated when you tab out of the cell. My subtotal command is creating new 'sum' formula's. Maybe I'll mess around with kicking the iterations and Max Change way down temporarily since my data range has no circular reference or formulas at all.
thanks again.