EXCLE不同單元格格式太多

1、cuidc 高級書童終極解決EXCEL“不同單元格格式太多”的問題瀏覽 303 次 創(chuàng)建時間 2008-05-08 00:32:13 作者 崔德春 在英文版中的提示是 Too many different cell formats. 一旦出現(xiàn)這個問題，文件可能無法打開。 微軟的這個錯誤可以出現(xiàn)在 EXCEL2003 及以前的各個版本，卻沒法提出解決方案，實(shí)在愚 蠢。 微軟只是在網(wǎng)站上說，系統(tǒng)給的限制應(yīng)該足夠了。廢話，足夠了還能有這個錯誤信息嗎？ 很多表格合并后一直到“保存成功”都看不到任何警告，等打開就不行了，災(zāi)難啊。搜索中文網(wǎng)站一無所獲，無非讓你手工修改格式。搜索 2 個多小時的英文網(wǎng)站，找

2、到很多比較容易的解決方案。解決方案一： 這里提供一個 Xlsgen Style reduction tool，2M$49.90 美元。 試用版每次處理一小部分， 我這成功地修復(fù)一個文件。 （ 有破解就好了 ） 運(yùn) 行時把 Working folder 改成 c: 對處理過的文件再反復(fù)處理 （ 如果是注冊版應(yīng)該一次解決 ）. 我發(fā)現(xiàn)試用版還悄悄把幾處內(nèi)容 改為他們的廣告 .免費(fèi)解決方案二：下載一個OPEN OFFICE打開后再保存成 Excel格式，很多老外都這么說，我沒有實(shí)踐過。 OpenOffice.Org 免費(fèi)下載。一個文件用 EXCEL 07打開正常，存成 2000 格式后就打不開。那就

3、用 07 打開，分成兩（或 三、四）部分存成 2000 格式。方案四：如果EXCEL文件能打開編輯，可以試驗(yàn)一下這個 VB程序，從老外的網(wǎng)站上抄來的。（用EXCEL 的運(yùn)行宏功能 ）Sub DeleteUnusedCustomNumberFormats()Dim Buffer As ObjectDim Sh As ObjectDim SaveFormat As VariantDim fFormat As VariantDim nFormat() As VariantDim xFormat As LongDim Counter As LongDim Counter1 As LongDim Cou

4、nter2 As LongDim StartRow As LongDim EndRow As LongDim Dummy As VariantDim pPresent As BooleanDim NumberOfFormats As LongDim AnswerDim c As ObjectDim DataStart As LongDim DataEnd As LongDim AnswerText As StringNumberOfFormats = 1000ReDim nFormat(0 To NumberOfFormats)AnswerText = Do you want to delet

5、e 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 FinitoWorksheets.Add.Move after:=Worksheets(Worksheets.Count)Worksheets(Worksheet

6、s.Count).Name = CustomFormatsWorksheets(CustomFormats).ActivateSet Buffer = Range(A2)Buffer.SelectnFormat(0) = Buffer.NumberFormatLocalCounter = 1DoSaveFormat = Buffer.NumberFormatLocalDummy = Buffer.NumberFormatLocalDoEventsSendKeys tab 3downenterApplication.Dialogs(xlDialogFormatNumber).Show Dummy

7、 nFormat(Counter) = Buffer.NumberFormatLocalCounter = Counter + 1Loop Until nFormat(Counter - 1) = SaveFormatReDim Preserve nFormat(0 To Counter - 2)Range(A1).Value = Custom formatsRange(B1).Value = Formats used in workbookRange(C1).Value = Formats not usedRange(A1:C1).Font.Bold = TrueStartRow = 3En

8、dRow = 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 CounterCounter = 0For Each Sh In ActiveWorkbook.WorksheetsIf Sh.Name = CustomFormats Then Exit ForFor Each c In S

9、h.UsedRange.Cells fFormat = c.NumberFormatLocalCells(EndRow,If Application.WorksheetFunction.CountIf(Range(Cells(StartRow, 2), 2), fFormat) = 0 ThenCells(StartRow, 2).Offset(Counter, 0).NumberFormatLocal = fFormat Cells(StartRow, 2).Offset(Counter, 0).Value = fFormatCounter = Counter + 1End IfNext c

10、Next ShxFormat = Range(Cells(StartRow, 2), Cells(EndRow, 2).Find().Row - 2 Counter2 = 0For Counter = 0 To UBound(nFormat) pPresent = FalseFor Counter1 = 1 To xFormatIf nFormat(Counter) = Cells(StartRow, 2).Offset(Counter1, 0).NumberFormatLocal Then pPresent = TrueEnd IfNext Counter1If pPresent = Fal

11、se ThenCells(StartRow, 3).Offset(Counter2, 0).NumberFormatLocal = nFormat(Counter)Cells(StartRow, 3).Offset(Counter2, 0).Value = nFormat(Counter)Counter2 = Counter2 + 1End IfNext CounterWith ActiveSheet.Columns(A:C).AutoFit.HorizontalAlignment = xlLeftEnd WithIf Answer = vbYes ThenDataStart = Range(Cells(1, 3), Cells(EndRow, 3).Find().Row + 1DataEnd = Cells(DataStart, 3).Resize(EndRow, 1).Find().Row - 1On Error Resume NextFor Each c I