《EXCLE不同单元格格式太多》由会员分享，可在线阅读，更多相关《EXCLE不同单元格格式太多（4页珍藏版）》请在金锄头文库上搜索。
1、cuidccuidc 高级书童终极解决终极解决 EXCEL“EXCEL“不同单元格格式太多不同单元格格式太多”的问题的问题浏览303次 创建时间 2008-05-08 00:32:13 - 作者 崔德春 -在英文版中的提示是 Too many different cell formats. 一旦出现这个问题，文件可能无法打开。 微软的这个错误可以出现在 EXCEL 2003 及以前的各个版本，却没法提出解决方案，实在 愚蠢。 微软只是在网站上说，系统给的限制应该足够了。废话，足够了还能有这个错误信息吗？ 很多表格合并后一直到“保存成功”都看不到任何警告，等打开就不行了，灾难啊。搜索中文网站一无
2、所获，无非让你手工修改格式。 搜索 2 个多小时的英文网站，找到很多比较容易的解决方案。解决方案一： http:/ 这里提供一个 Xlsgen Style reduction tool，2M $49.90 美元。试用版每次处理一小部分，我这成功地修复一个文件。(有破解就 好了) 运行时把 Working folder 改成 c: 对处理过的文件再反复处理(如果是注册版应该一次解决).我发现试用版还悄悄把几处内容 改为他们的广告. 免费解决方案二： 下载一个 OPEN OFFICE，打开后再保存成 Excel 格式，很多老外都这么说，我没有实践过。OpenOffice.Org 免费下载。 方案三
3、： 一个文件用 EXCEL 07 打开正常，存成 2000 格式后就打不开。那就用 07 打开，分成两（或 三、四）部分存成 2000 格式。 方案四： 如果 EXCEL 文件能打开编辑，可以试验一下这个 VB 程序，从老外的网站上抄来的。(用 EXCEL 的运行宏功能)Sub DeleteUnusedCustomNumberFormats() Dim Buffer As Object Dim Sh As Object Dim SaveFormat As Variant Dim fFormat As Variant Dim nFormat() As Variant Dim xFormat As
4、 Long Dim Counter As Long Dim Counter1 As Long Dim Counter2 As Long Dim StartRow As LongDim EndRow As Long Dim Dummy As Variant Dim pPresent As Boolean Dim NumberOfFormats As Long Dim Answer Dim c As Object Dim DataStart As Long Dim DataEnd As Long Dim AnswerText As StringNumberOfFormats = 1000 ReDi
5、m nFormat(0 To NumberOfFormats) AnswerText = “Do you want to delete unused custom formats from the workbook?“ AnswerText = AnswerText & Chr(10) & “To get a list of used and unused formats only, choose No.“ Answer = MsgBox(AnswerText, 259) If Answer = vbCancel Then GoTo FinitoOn Error GoTo Finito Wor
6、ksheets.Add.Move after:=Worksheets(Worksheets.Count) Worksheets(Worksheets.Count).Name = “CustomFormats“ Worksheets(“CustomFormats“).Activate Set Buffer = Range(“A2“) Buffer.Select nFormat(0) = Buffer.NumberFormatLocal Counter = 1 Do SaveFormat = Buffer.NumberFormatLocal Dummy = Buffer.NumberFormatL
7、ocal DoEvents SendKeys “tab 3downenter“ Application.Dialogs(xlDialogFormatNumber).Show Dummy nFormat(Counter) = Buffer.NumberFormatLocal Counter = Counter + 1 Loop Until nFormat(Counter - 1) = SaveFormatReDim Preserve nFormat(0 To Counter - 2)Range(“A1“).Value = “Custom formats“ Range(“B1“).Value =
8、“Formats used in workbook“ Range(“C1“).Value = “Formats not used“ Range(“A1:C1“).Font.Bold = TrueStartRow = 3EndRow = 16384For Counter = 0 To UBound(nFormat) Cells(StartRow, 1).Offset(Counter, 0).NumberFormatLocal = nFormat(Counter) Cells(StartRow, 1).Offset(Counter, 0).Value = nFormat(Counter) Next
9、 CounterCounter = 0 For Each Sh In ActiveWorkbook.Worksheets If Sh.Name = “CustomFormats“ Then Exit For For Each c In Sh.UsedRange.Cells fFormat = c.NumberFormatLocal If Application.WorksheetFunction.CountIf(Range(Cells(StartRow, 2), Cells(EndRow, 2), fFormat) = 0 Then Cells(StartRow, 2).Offset(Coun
10、ter, 0).NumberFormatLocal = fFormat Cells(StartRow, 2).Offset(Counter, 0).Value = fFormat Counter = Counter + 1 End If Next c Next ShxFormat = Range(Cells(StartRow, 2), Cells(EndRow, 2).Find(“).Row - 2 Counter2 = 0 For Counter = 0 To UBound(nFormat) pPresent = False For Counter1 = 1 To xFormat If nF
11、ormat(Counter) = Cells(StartRow, 2).Offset(Counter1, 0).NumberFormatLocal Then pPresent = True End If Next Counter1 If pPresent = False Then Cells(StartRow, 3).Offset(Counter2, 0).NumberFormatLocal = nFormat(Counter) Cells(StartRow, 3).Offset(Counter2, 0).Value = nFormat(Counter) Counter2 = Counter2
12、 + 1 End If Next Counter With ActiveSheet.Columns(“A:C“) .AutoFit .HorizontalAlignment = xlLeft End With If Answer = vbYes Then DataStart = Range(Cells(1, 3), Cells(EndRow, 3).Find(“).Row + 1 DataEnd = Cells(DataStart, 3).Resize(EndRow, 1).Find(“).Row - 1On Error Resume Next For Each c In Range(Cells(DataStart, 3), Cells(DataEnd, 3).Cells ActiveWorkbook.DeleteNumberFormat (c.NumberFormat) Next c End If Finito: Set c = Nothing Set Sh = Nothing Set Buffer = Nothing End Sub1
