Number two in the series of short posts (I know there are some who are amazed by that) on useful hacks, is one that I didn’t write. Yes, this is much easier than actually doing it myself.
This post comes from my good friend and colleague Dave Farnsworth with assistance by Lancen Lachance and is a fix for Excel and its too-many-styles-in-the-workbook error. This solution is almost as good as his presentation and whitepaper on “Planning Tables, What’s Behind the Curtain?” at this year’s ODTUG Kaleidoscope conference. If you do anything with Plannng, I strongly urge you drink deeply at the well of his whitepaper. It is made of Au (Which would be rather difficult if not deadly to drink – I am mixing metaphors but you get the idea).
In his own words
Rather than prattle on, I’ll let the distinctly more succinct Dave take over:
One of the quirks of Office 2007 is that Excel styles can go crazy. Like most places, our users keep workbooks for years, updating them every month and copying data back & forth. Apparently with [Excel] 2007, when you copy one cell all of the workbook styles also get copied. We found a workbook with 40k styles. When a cell of data was copied from the book to a SmartView form Excel screamed "no can do, too many styles".
The following macro will delete and/or count styles. There are many of these available for sale on the net. You can have mine free.
Caution: the data & formuli remain but you may lose some formatting.
And his own code
' DeleteStyles Macro
Dim styT As Style
Dim lngCount As Long
Dim intRet As Integer
Dim bDeletestyles As Boolean
Dim strMsg As String
Const DELETESTYLES As String = "Number of custom styles deleted: "
Const COUNTEDSTYLES As String = "Number of custom styles in workbook: "
bDeletestyles = False
intRet = MsgBox("Do you wish to delete the styles? '", vbYesNo)
If intRet = vbYes Then
bDeletestyles = True
'loop through the workbook styles
For Each styT In ActiveWorkbook.Styles
If Not styT.BuiltIn Then 'ignore built in styles
If bDeletestyles Then styT.Delete
lngCount = lngCount + 1
If bDeletestyles Then
strMsg = DELETESTYLES
strMsg = COUNTEDSTYLES
intRet = MsgBox(strMsg & lngCount, vbOK)
And that’s it – when you have the problem, it’s a stinkeroo. Dave’s code goes through and either tells you how many you have, or deletes ‘em. Your choice.
And he’s right – there are commercial products out there that do this, which you no longer need because guess what, you just got it for free, and even other code examples that do the same thing.
So why show Dave’s? Because it’s debugged, it’s a good example of well written and architected code, and we in the Oracle EPM world are likely to run into this problem and here you are with this example.
Enjoy the hack and thank Dave by hiring him if his current clients ever let go of him. :)