Is it possible to automatically repair corrupt Excel workbooks?

Question

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

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.

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

Answer 1

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.

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