Excel出现“不同的单元格格式太多”解决方案.doc

Excel出现“不同的储存格式”解決方案原因：一个工作表常时间的使用，或经多人不同的编辑会使表格的格式日积月累越来越多，以至在2003以下的版的用户打不开（或格式超过4000种）大家可以去看看自己表格是否有过多的格式（色彩也是格式的一种）可以在单元格样式中看到。如何来清除过多的格式呢解決方案：打开出现“太多不同的储存格格式”的Excel文件；第一步：“开发工具”“进入VB(Visual Basic)”（如下图）第二步：打开代码窗口，建模块，在代码窗口里將Sub RebuildDefaultStyles(见附档)档案中的內容copy进去，替换原來的內容第三步：运行代码（点击运行里选择“运行子过程/用户窗体”或直接点击如图箭头所示）（如图）第四步：看看工作表的格式是不是已去除了，如下图那些在使用过程中留下的格式已除去了，只剩下Excel默认自带的格式。你的工作表如果格式太多了，最好清一清，工作时尽量不要更换太多不一样的格式。使用上述的办法不会对你的工作表内容更改。记得使用过后把代码删了。（文件占有空间也会变小）附档：Sub RebuildDefaultStyles() The purpose of this macro is to remove all styles in the active workbook and rebuild the default styles. It rebuilds the default styles by merging them from a new workbook. Dimension variables. Dim MyBook As Workbook Dim tempBook As Workbook Dim CurStyle As Style Set MyBook to the active workbook. Set MyBook = ActiveWorkbook On Error Resume Next Delete all the styles in the workbook. For Each CurStyle In MyBook.Styles If CurStyle.Name Normal Then CurStyle.Delete Select Case CurStyle.Name Case 20% - Accent1, 20% - Accent2, _ 20% - Accent3, 20% - Accent4, 20% - Accent5, 20% - Accent6, _ 40% - Accent1, 40% - Accent2, 40% - Accent3, 40% - Accent4, _ 40% - Accent5, 40% - Accent6, 60% - Accent1, 60% - Accent2, _ 60% - Accent3, 60% - Accent4, 60% - Accent5, 60% - Accent6, _ Accent1, Accent2, Accent3, Accent4, Accent5, Accent6, _ Bad, Calculation, Check Cell, Comma, Comma 0, Currency, _ Currency 0, Explanatory Text, Good, Heading 1, Heading 2, _ Heading 3, Heading 4, Input, Linked Cell, Neutral, Normal, _ Note, Output, Percent, Title, Total, Warning Text Do nothing, these are the default styles Case Else CurStyle.Delete End Select Next CurStyle Open a new workbook. Set tempBook = Workbooks.Add Disable alerts so you may merge changes to the Normal style from the new workbook. Application.DisplayAlerts = False Merge styles from the new workbook into the existing workbook. MyBook.Styles.Merge Workbook:=tem