Stupid programming tricks #2

Introduction

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

Sub DELETESTYLES()

'

' 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

    End If

    '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

        End If

    Next styT

    If bDeletestyles Then

        strMsg = DELETESTYLES

    Else

        strMsg = COUNTEDSTYLES

    End If

        intRet = MsgBox(strMsg & lngCount, vbOK)

End Sub

Conclusion

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.  :)