是否可以自动修复损坏的Excel工作簿？ [英] Is it possible to automatically repair corrupt Excel workbooks?

问题描述

我正在使用SPSS 15创建几个Excel报表，然后使用Excel宏进行合并。不幸的是，特定的SPSS版本生成Excel 2007及以上版本不易读取的.xls文件。 Excel 2003将这些文件搞乱，但更新的Excel版本显示两个错误消息。首先是Excel在filename.xls中发现无法读取的内容，是否要恢复此工作簿的内容？单击是后面是文件错误：数据可能已丢失。不幸的是，这些错误消息导致我的宏退出在第一个文件，错误代码为1004.这是我用来打开我的Excel文件的代码：

I am using SPSS 15 to create several Excel reports which I am then consolidating using an Excel macro. Unfortunately that particular SPSS version produces .xls files that aren't easily readable for Excel 2007 and up. Excel 2003 gobbles those files up just fine, but newer Excel versions display two error messages. First up is "Excel found unreadable content in filename.xls. Do you want to recover the contents of this workbook?". After clicking yes this is followed by "File Error: data may have been lost". Unfortunately these error messages cause my macro to quit at the first file with error code 1004. This is the code I am using to open my Excel files:

If Len(Dir(ThisWorkbook.Path + "\filename.xls")) <> 0 Then 
    Workbooks.Open Filename:=ThisWorkbook.Path + "\filename.xls"
End If

$ b $我已经与IBM（SPSS供应商）核对了，他们告诉我，这个特定的问题在SPSS 16中是修复的，但由于业务原因，升级不在卡上。现在有一个手动解决方法，涉及打开文件并再次保存，但是几十个文件显然没有任何乐趣。因此，我正在寻找一种在我的宏中自动修复这些损坏的工作簿的方法。

I checked with IBM (SPSS vendors) and they told me that this particular issue is fixed in SPSS 16 but because of business reasons an upgrade is not on the cards. Now there is a manual workaround which involves opening the files and saving again but with dozens of files that's obviously no fun. Therefore I am looking for a way to automatically repair those corrupt workbooks in my macro.

更多信息：我们正在使用Excel 2010，Excel 2003不可用。示例文件可在此处获取： https://onedrive.live.com/ ？cid = 52106BC267261CBF& id = 52106BC267261CBF！292

Further information: we are using Excel 2010 at work, Excel 2003 is not available. A sample file is available here: https://onedrive.live.com/?cid=52106BC267261CBF&id=52106BC267261CBF!292

推荐答案

该文件看起来相当困惑，BIFF验证工具报告一个不正确的BOF头，所以它令人印象深刻的Excel 2003可以打开它。

That file seems fairly screwed up, the BIFF validation tool reports an incorrect BOF header so its impressive Excel 2003 can open it at all.

我可以通过Jet OLEDB提供者将您的文件作为ISAM数据库读取，以便您可以选择以这种方式读取文件（或使用此方法生成一个CSV文件进行处理）

I can read your file as an ISAM database via the Jet OLEDB provider so you may choose to read the file this way (or use this approach to generate a CSV file for processing)

Dim cn As Object, rs As Object
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=MICROSOFT.JET.OLEDB.4.0;Data Source=C:\temp\DebugFile.xls;Extended Properties=""Excel 8.0;HDR=Yes;"""

Set rs = cn.Execute("SELECT * FROM [DebugFile$]")

Do While Not rs.EOF
    Debug.Print rs.Fields(0).Value
    rs.MoveNext
Loop

这篇关于是否可以自动修复损坏的Excel工作簿？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！