+ Post New Thread
Results 1 to 7 of 7
Office Software Thread, Unique Cell Styles in Technical; I have a problem when saving a report from 2007 format to 2003. Compatability checker throws up an error about ...
  1. #1
    CAM
    CAM is offline

    CAM's Avatar
    Join Date
    Mar 2008
    Location
    Burgh Heath, Surrey
    Posts
    4,022
    Thank Post
    803
    Thanked 346 Times in 270 Posts
    Blog Entries
    60
    Rep Power
    278

    Unique Cell Styles

    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.

  2. #2
    jamesfed's Avatar
    Join Date
    Sep 2009
    Location
    Reading
    Posts
    2,186
    Thank Post
    133
    Thanked 340 Times in 287 Posts
    Rep Power
    84
    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/d...displaylang=en

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

  3. #3
    CAM
    CAM is offline

    CAM's Avatar
    Join Date
    Mar 2008
    Location
    Burgh Heath, Surrey
    Posts
    4,022
    Thank Post
    803
    Thanked 346 Times in 270 Posts
    Blog Entries
    60
    Rep Power
    278
    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.
    Last edited by CAM; 13th January 2010 at 06:54 PM.

  4. #4
    CAM
    CAM is offline

    CAM's Avatar
    Join Date
    Mar 2008
    Location
    Burgh Heath, Surrey
    Posts
    4,022
    Thank Post
    803
    Thanked 346 Times in 270 Posts
    Blog Entries
    60
    Rep Power
    278
    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.

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

  5. #5
    ChrisH's Avatar
    Join Date
    Jun 2005
    Location
    East Lancs
    Posts
    4,996
    Thank Post
    120
    Thanked 280 Times in 258 Posts
    Rep Power
    106
    Quote Originally Posted by CAM View Post
    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.

    Code:
    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

    Code:
    Application.ScreenUpdating = False
    
    	'YOUR CODE
    
    	Application.ScreenUpdating = True
    and some commenting out would have saved you abusing your enter key

    Code:
    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

  6. Thanks to ChrisH from:

    CAM (14th January 2010)

  7. #6
    Mr.Ben's Avatar
    Join Date
    Jan 2008
    Location
    A Pirate Ship
    Posts
    941
    Thank Post
    182
    Thanked 157 Times in 126 Posts
    Blog Entries
    2
    Rep Power
    65
    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.

  8. Thanks to Mr.Ben from:

    CAM (14th January 2010)

  9. #7
    CAM
    CAM is offline

    CAM's Avatar
    Join Date
    Mar 2008
    Location
    Burgh Heath, Surrey
    Posts
    4,022
    Thank Post
    803
    Thanked 346 Times in 270 Posts
    Blog Entries
    60
    Rep Power
    278
    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.

SHARE:
+ Post New Thread

Similar Threads

  1. [MS Office - XP] Excel Macro - If Cell Value
    By ahuxham in forum Office Software
    Replies: 0
    Last Post: 18th December 2009, 10:27 AM
  2. [Website] Learning styles
    By browolf in forum Jokes/Interweb Things
    Replies: 3
    Last Post: 4th December 2009, 12:45 PM
  3. JCE Editor No Styles
    By Zorba in forum EduGeek Joomla 1.5 Package
    Replies: 12
    Last Post: 13th October 2008, 08:57 PM
  4. CSS & Styles
    By phillipmillward in forum EduGeek Joomla 1.5 Package
    Replies: 1
    Last Post: 28th September 2008, 07:30 PM
  5. CSS Styles
    By crazy in forum EduGeek Joomla 1.0 Package
    Replies: 3
    Last Post: 12th March 2008, 06:54 AM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •