EXCLE不同单元格格式太多
cuidc高级书童
终极解决EXCEL“不同单元格格式太多”的问题
浏览303次 创建时间 2008-05-08 00:32:13
 
---- 作者 崔德春 ----

在英文版中的提示是 Too many different cell formats.
一旦出现这个问题，文件可能无法打开。
微软的这个错误可以出现在EXCEL 2003 及以前的各个版本，却没法提出解决方案，实在愚蠢。
微软只是在网站上说，系统给的限制应该足够了。废话，足够了还能有这个错误信息吗？
很多表格合并后一直到“保存成功”都看不到任何警告，等打开就不行了，灾难啊。

搜索中文网站一无所获，无非让你手工修改格式。
搜索致敬抗疫英雄手抄报内容2个多小时的英文网站，到很多比较容易的解决方案。

解决方案一：
xlsgenreduction.arstdesign 这里提供一个 Xlsgen Style reduction tool，2M $49.90美元。试用版每次处理一小部分，我这成功地修复一个文件。(有破解就好了中东指哪些国家) 运行时把Working folder 改成c:\
对处理过的文件再反复处理(如果是注册版应该一次解决).我发现试用版还悄悄把几处内容改为他们的广告.
免费解决方案二：
下载一个OPEN OFFICE，打开后再保存成Excel格式，很多老外都这么说，我没有实践过。 OpenOffice.Org 免费下载。
方案三：
一个文件用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 Long
Dim Counter As Long
Dim Counter1 As Long
Dim Counter2 As Long
Dim StartRow As Long
Dim 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 String

NumberOfFormats = 1000
ReDim 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 Finito

On Error GoTo Finito
Worksheets.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.NumberFormatLocal
DoEvents
SendKeys "{tab 3}{down}{enter}"
Application.Dialogs(xlDialogFormatNumber).Show Dummy
nFormat(Counter) = Buffer.NumberFormatLocal
Counter = Counter + 1
Loop Until nFormat(Counter - 1) = SaveFormat

ReDim Preserve nFormat(0 To Counter - 2)

Range("A1").Value = "Custom formats"
Range("B1").Value = "Formats used in workbook"
Range("C1").Value = "Formats not used"
Range("A1:C1").Font.Bold = True

StartRow = 3
EndRow = 16384


For Counter = 0 To UBound(nFormat)
Cells(StartRow, 1).Offset(Counter, 0).NumberFormatLocal = nFormat(Counter)
Cells(StartRow, 1).Offset(Counter, 0).Value = nFormat(Counter)
Next Counter

Counter = 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(Counter, 0).NumberFormatLocal = fFormat
Cells(StartRow, 2).Offset(Counter, 0).Value = fFormat
Counter = Counter + 1
End If
Next c
Next Sh

xFormat = 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 nFormat(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 + 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 - 1
On 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

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。