[ms office - 2007] Unique Cell Styles

[CAM]

I have a problem when saving a report from 2007 format to 2003. Compatability checker throws up an error about too many unique cell styles when I try to save the workbook and when I open the 2003 copy, all the formatting (borders, fill, number type, etc) I laboriously applied has gone!

 

I checked the Cell Styles and on a report where it converts happily, there is nothing there. On this troublesome report there is hundreds of cell styles called Normal followed by a load of numbers. On trying to delete the styles, nothing happens.

 

Any idea how to preserve my beloved formatting without losing it and having to reformat the entire document again?

 

Thanks.

[jamesfed]

Any particular reason you need to save to 2003?

If its just that thats what your staff/students/whoever uses at home they can download and install the compatiablity pack-

http://www.microsoft.com/downloads/details.aspx?FamilyId=941b3470-3ae9-4aee-8f43-c6bb74cd1466&displaylang=en

 

It means 2003 can open 2007 documents (don't know if it will show the fancy formating though).

[CAM]

We have no guarantee staff will install it on their laptops or home systems though. I installed it to download reports exceeding the column limit in 2003 so I'm one of the few using 2007. Was told to save in 2003 format though.

 

EDIT - I have tried to copy the data to a new sheet but the custom styles keep following it around. As soon as the data goes in the styles box fills up with the Normal styles again even if I use the copy saved in 2003 format.

Edited January 13, 2010 by CAM

[CAM]

It appears Excel created such a vast quantity of custom styles for some unfathomable reason, that this macro I found took over 15mins to run. I had to find something heavy and put it on the enter key to close the dialog box.

 

Sub StyleKill()
   Dim styT As Style
   Dim intRet As Integer

   For Each styT In ActiveWorkbook.Styles
       If Not styT.BuiltIn Then
           intRet = MsgBox("Delete style '" & styT.Name & "'?", vbYesNo)
           If intRet = vbYes Then styT.Delete
       End If
   Next styT
End Sub

 

This kept the bold, orientation, borders, etc intact too. Also vastly improved loading times of the workbook.

 

Macro taken from Deleting Unwanted Styles (Tips.Net)

[ChrisH]

It appears Excel created such a vast quantity of custom styles for some unfathomable reason, that this macro I found took over 15mins to run. I had to find something heavy and put it on the enter key to close the dialog box.

 

Sub StyleKill()
   Dim styT As Style
   Dim intRet As Integer

   For Each styT In ActiveWorkbook.Styles
       If Not styT.BuiltIn Then
           intRet = MsgBox("Delete style '" & styT.Name & "'?", vbYesNo)
           If intRet = vbYes Then styT.Delete
       End If
   Next styT
End Sub

 

This kept the bold, orientation, borders, etc intact too. Also vastly improved loading times of the workbook.

 

Macro taken from Deleting Unwanted Styles (Tips.Net)

 

You can speed up macros a lot by using the following code

 

Application.ScreenUpdating = False

'YOUR CODE

Application.ScreenUpdating = True

 

and some commenting out would have saved you abusing your enter key

 

Sub StyleKill()
   Dim styT As Style
   Dim intRet As Integer

   For Each styT In ActiveWorkbook.Styles
       If Not styT.BuiltIn Then
          ' intRet = MsgBox("Delete style '" & styT.Name & "'?", vbYesNo)
          ' If intRet = vbYes Then 
styT.Delete
       End If
   Next styT
End Sub

[Mr.Ben]

Can you make the compatibility pack link available on your website?

 

It's now almost 4 years since they released it. I'm afraid that I would have no patience with staff who have not installed it yet.

[CAM]

That may be a good idea. Still careful about releasing it in XSLX format though as I know there will be some who forget to.

 

Chris, thanks for the macro changes. I had one of those "start the macro and realise oh dear" moments by which time jamming enter down was the easier option. Needed a break from the screen anyway.