locked
too many cell formats RRS feed

  • Question

  • Hi Guys,

    I have excel 2010 s/s which I need to work with 2003. The problem is that when I try to open the s/s in 2003 I have too many different cell formats error. That is strange because my s/s in 2010 is not that big. I tried to clean all formats going tab by tab, saved it and tried to reopen in 2003. No difference still the same error. Any ideas is appreciated. Unfortunately I can not use the popular tool XLStylesTool on my work.

    Thanks,

    Ilya

    • Moved by Damon Zheng Wednesday, March 27, 2013 12:57 PM
    Tuesday, March 19, 2013 12:40 PM

Answers

All replies

  • For an an attempt at a quick fix, did you try to do an Open and Repair in 2010 ?
    Tuesday, March 19, 2013 2:31 PM
  • I do not have a problem in 2010, if I open it it is working fine. I have a problem opening in 2003.

    Tuesday, March 19, 2013 2:46 PM
  • ilyaw-

    Are you saving in .xls format from 2010? I would trust the backward compatible save feature before I would think that 2003 could open a 2010 file directly with whatever post-production add-ons they have for forward compatibility.

    Also, how many graphs are in your file? Even with a straight xls file, I suspect that 2010 can pack more into a file than 2003 can accept. It has been a few years since I used 2003, but I recall hitting the limit in under 40 graphs, as 2003 used each font in each graph as an individual format.

    Tuesday, March 19, 2013 2:59 PM
  • Thanks for reply Ker01,

    I'm saving in xls format working in 2010. I do not have any graphs at all :)

    Tuesday, March 19, 2013 3:05 PM
  • Now I remember this problem....and there is a simple solution: run xlcleaner
    I used it once..it works !

    xlcleaner.exe

    • Proposed as answer by Syswizard Friday, March 22, 2013 1:14 AM
    • Marked as answer by Damon Zheng Wednesday, March 27, 2013 12:50 PM
    • Unmarked as answer by Damon Zheng Wednesday, March 27, 2013 12:51 PM
    Tuesday, March 19, 2013 3:32 PM
  • Thanks for reply Syswizard

    Cannot open your link from the work. Will try to do it at home. For now I rebuilt everything using 2003 :) That was a pain but looks like no other way...

    • Marked as answer by Damon Zheng Wednesday, March 27, 2013 12:51 PM
    • Unmarked as answer by Damon Zheng Wednesday, March 27, 2013 12:51 PM
    Tuesday, March 19, 2013 3:44 PM
  • There are several ways to resolve this issue, but the cleanest and easiest method is to remove all extra styles (I have seen workbooks with 9000+ styles )

    with the following simple VBA code you can remove all non-builtin styles and in the vast majority of cases this fixes the error.

    Sub RemoveStyles()
    Dim li as long
    On Error Resume Next

    With ActiveWorkbook
    For li = .Styles.Count To 1 Step -1
    If Not .Styles(li).BuiltIn Then
    .Styles(li).Delete
    End If
    Next
    End With
    End Sub

    Ryan Shuell

    • Marked as answer by Damon Zheng Wednesday, March 27, 2013 12:50 PM
    • Unmarked as answer by Damon Zheng Wednesday, March 27, 2013 12:51 PM
    Saturday, March 23, 2013 4:20 AM
  • and in the vast majority of cases this fixes the error.
    Ryan Shuell

    Of course Ryan, but at a HUGE PRICE !!

    All of those styles must now be re-created from scratch.
    Wow, what a waste of time...all due to an MSFT bug.

    Monday, March 25, 2013 1:20 AM
  • Hello ilyaw,

    We appreciate your participation in this forum, but this forum is for development issues regarding Excel. For non-development issues, please try Microsoft Community: http://answers.microsoft.com/en-us/office/forum/excel

    This thread will be moved to Off-topic. Thanks for your understanding.

    Regards,


    Damon Zheng
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Just Karl Wednesday, November 6, 2013 11:26 PM
    Wednesday, March 27, 2013 12:57 PM