楼主
yd0209 |
 基本审计程序.rar
在英文版中的提示是 Too many different cell formats.
一旦出现这个问题，文件可能无法打开。
微软的这个错误可以出现在EXCEL 2003 及以前的各个版本，却没法提出解决方案，实在愚蠢。
微软只是在网站上说，系统给的限制应该足够了。废话，足够了还能有这个错误信息吗？
很多表格合并后一直到“保存成功”都看不到任何警告，等打开就不行了，灾难啊。
搜索中文网站一无所获，无非让你手工修改格式。
搜索2个多小时的英文网站，找到很多比较容易的解决方案。
解决方案一：
http://xlsgenreduction.arstdesign.com
对处理过的文件再反复处理(如果是注册版应该一次解决).我发现试用版还悄悄把几处内容改为他们的广告.
免费解决方案二：
下载一个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 Sub
我上传一个附件，这个工作簿如果再往里面添加内容，就会引起格式丢失的问题，麻烦版主把这个106行的VBA程序放进去，我看看好使不好使。
演示.rar
|