Is it possible to automatically repair damaged Excel workbooks?

Question

I use SPSS 15 to create several Excel reports, which I then compile using an Excel macro. Unfortunately, this version of SPSS creates .xls files that are easy to read for Excel 2007 and later. Excel 2003 accumulates these files, but newer versions of Excel display two error messages. First of all, "Excel found unreadable content in filename.xls. Do you want to restore the contents of this workbook?" After clicking yes, it is followed by “File error: data may be lost.” Unfortunately, these error messages cause my macro to exit in the first file with error code 1004. This is the code I use to open my Excel files:

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

I checked with IBM (SPSS providers) and they told me that this problem was fixed in SPSS 16, but for business reasons the update is not on the maps. Now there is a manual workaround that involves opening files and saving again, but with dozens of files that are obviously not interesting. Therefore, I am looking for a way to automatically repair these damaged books in my macro.

Additional information: we use Excel 2010 at work, Excel 2003 is not available. An example file can be found here: https://onedrive.live.com/?cid=52106BC267261CBF&id=52106BC267261CBF!292

Answer 1

This file seems rather confusing, the BIFF checker reports an incorrect BOF header, so its impressive Excel 2003 can open it at all.

I can read your file as an ISAM database through the Jet OLEDB provider, so you can choose a file to read this way (or use this approach to create 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