Excel Styles and formats

Hopefully we all know what a disaster zone this area is so I won’t dwell on that is this post.

I have a specific question related to an ongoing support incident raised with MS.

We have some (admittedly old) workbooks with unusual style names, that cannot be deleted via code. In fact just trying to access them causes an error (in VBA, C#, C and XLM).

I wonder has anyone else seen this? does anyone know the cause? (To me it looks like an old culture/unicode issue simply because I have seen oriental and Cyrillic characters elsewhere in the styles list. (MS feel it came form a non MS source)

excelstyle1

In Excel 2003 these styles can cause the file to refuse to open with invalid file format or unreadable content type errors. 2010 opens them fine, but you still can’t delete them via the UI or code.

xmlstyle

The only way we have found so far is to save as xlsm and manually edit the xml to remove those rows. There are other tools but for this post I really just want to know if anyone else has seen these uneditable styles and if anyone knows the cause?

thanks for your help

cheers

simon

About these ads

10 Responses to “Excel Styles and formats”

  1. john owen (@John_Owen_) Says:

    I use this …

    Sub ResetCellStyles()

    Dim objStyle As Style
    Dim wkbTemp As Workbook
    Dim wkbActive As Workbook

    ‘ First, remove all styles other than Excel’s own.
    ‘ they may have arrived from pasting from other workbooks

    For Each objStyle In ActiveWorkbook.Styles

    ShowDetailMessage vbNullString
    On Error Resume Next
    If Not objStyle.BuiltIn Then objStyle.Delete
    On Error GoTo 0

    Next

    ‘Second, revert the remaining styles to Excel’s default for a new workbook

    Set wkbActive = ActiveWorkbook
    Set wkbTemp = Workbooks.Add
    wkbActive.Styles.Merge wkbTemp
    wkbTemp.Close savechanges:=False

    ActiveWorkbookStop “Finishing …”, False

    Set objStyle = Nothing
    Set wkbTemp = Nothing
    Set wkbActive = Nothing

    End Sub

  2. Simon Says:

    I have lots of code in lots of languages to delete normal styles, my question is do you ever come across any that this will not delete due to strange names.
    (Your code (and our code) fails to delete over 200 of these styles).
    (nice try though… :-)

  3. john owen (@John_Owen_) Says:

    Is it worth perhaps converting the style names to double byte and then referencing them by name to delete them?

  4. Simon Says:

    no, I usually go by index and that fails too, its not the name in the OM its deeper in the Excel codebase

  5. excelandaccess Says:

    What are the other tools mentioned above?

    Christopher

  6. sysmod Says:

    Simon,
    I’ve seen problem *Names* that came from Asian sources, but not yet the *Styles* you found above, that can not be deleted.

    What else you up to these days?

    P.S. Nicely done John, that’s uncannily like my code at
    http://answers.microsoft.com/en-us/office/forum/office_2007-excel/excel-2007-spontaneously-formats-entire-work-book/1ae533af-ecee-41fa-866a-2590343a111d

  7. Simon Says:

    Christopher
    This is good for .xlsm/x, it goes outside the OM so is fast and clears them all out:

    http://sergeig888.wordpress.com/2011/03/21/net4-0-version-of-the-xlstylestool-is-now-available/

    this is good for cell format issues in .xls:

    http://xlsgenreduction.arstdesign.com/index_en.html#Download

    Definitely recommending .xlsm/x now, had so much crap with the binaries, its not worth it, even for the perf improvements.

    POB – just about to move back from lndn to ch, ready for a non IT summer. you?

    • sysmod Says:

      An IT summer for me, looking forward to an offpeak break later.
      It’ll be nice for you to be away from the heat of a London summer.
      Mind you given the uncertain weather many Londoners might be hoping for a warm summer!

      Amuse-toi bien en Suisse! J’ai envie d’être là.

  8. excelandaccess Says:

    A non IT summer, does that mean you will not be working?

    Thank you for the additional info above.

    I love your blog. Great content.

    Have a great weekend.

    Christopher

  9. Jim Says:

    Yes. I’ve seen this corruption for years. Don’t know what caused it. My employer (large multinational) engaged Microsoft and they couldn’t fix it. The best they could do was come up with VBA code but, of course, that doesn’t touch the invalid names.

    Corrupt Style names are often accompanied by corrupt Range names.

    My method to fix these files is:
    1. convert to xlsx
    2. delete all bad range names 1st (I use JKPs Name Manager)
    3. pull the xl\Styles.xml file out of the file package (7-zip)
    4. Use XML Notepad to delete all cellStyles that don’t have a “builtinId” attribute
    5. Save and move the Styles.xml file back into the package
    6. Save back to xls if you need to

    The workbook is left with the default styles.

    I’ve never touched the Xfs nodes. This is where the styles are actually defined. Technically, since we deleted the style names there’s no need for the definitions and they could be deleted. But, I never have and have not experienced any ill effects.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Follow

Get every new post delivered to your Inbox.

Join 64 other followers

%d bloggers like this: