Want to know The Truth About CPM?

15 September 2010

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

No comments: