7

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

RubberDuck
  • 11,933
  • 4
  • 50
  • 95
Haris
  • 778
  • 2
  • 9
  • 20
  • can you programmatically open the file, set displayAlerts = false and save the wb? –  Jun 04 '14 at 14:37
  • Unfortunately, no. Any attempt to access the file progammatically via VBA gives me a 1004 error code and setting Application.DisplayAlerts = False in the Excel containing my macro doesn't work either. – Haris Jun 04 '14 at 14:42
  • I am not able to reproduce the problem. Any change you can upload a sample book which would let me repro this? –  Jun 04 '14 at 14:43
  • Here you go: https://onedrive.live.com/?cid=52106BC267261CBF&id=52106BC267261CBF!292 – Haris Jun 04 '14 at 14:54
  • 1
    I found if I deleted 1 cell on sheet DebugFile and saved, everything then works OK - now I can open with 2003 & 2010, no problem. This must cause Excel to do some sort of "rebuild" of the file, and remove the corruption. Just guessing! – DaveU Jun 05 '14 at 02:05

1 Answers1

7

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
Alex K.
  • 171,639
  • 30
  • 264
  • 288