Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel 97 won't read the file

Status
Not open for further replies.

ukjane

IS-IT--Management
Jun 7, 2001
101
GB
I have a user in our accounts dept who has an excel s/sheet he has used for some months. One day he just can't open it, illegal operation - program will be shutdown (useful message). He is using Excel 97.

He has emailed the file to me and I open it no problem - i;m Excel 2002 (XP).

The file doesn't contain links, it has 6 simple worksheets, with accounting data in it, fancy colours and so on. File is 2MB in size.

I have tried opening it on another PC running Excel 97 and same problem - illegal operation. So it's just Excel 97 that won't open the file - even though it was working until a couple of days ago
I have tried copying the data into another workbook and saving it, no joy. Also tried saving it in the Excel 95-2000 format in XP, this makes it about 4MB and still same problem.

If we remove some of the worksheets and re-save, it opens fine. So it seems to be the amount of data or the size of the file that it responsible or indeed some part of the workbook is corrupt.

Can anyone suggest a cure - or this a upgrade to Excel 2000 kind of thing.
 
Have the sheets been created by copying other sheets? If you take a sheet and copy it and then copy that and then copy that ... about 30 times Excel 97 will corrupt.

Open in Excel 2000 and press ALT F11 to go to the VBA environment and look at the properties data for each sheet you will see a Name value and a separate (Name) value. In Excel 97 when you copy the (Name) value is the previous one with a 1 on the end. Eventually Excel runs out of space to add more 1 characters. In Excel 2000 you can simply change the name values to something shorter.
 
I have check the sheet names in VBA and they are sheet1 sheet2 etc so must be originals.
 
I've had problems with Excel 97 crashing when running large files but it's happened when VB code is running.

Are there any auto_open or workbook_open macros in the file? If so try disabling them in XP and opening the file again in 97. If it opens OK there will be one line of code that Excel has suddenly taken exception to that is causing the crash - come back and I'll give you some examples to look for.
 
There are no macros or VB code in the file. Basically it's very simple thing really. Just tonnes of data and all prettied up with colours etc.
 
So the next thought is that it's too 'prettied up'. Excel 97 doesn't like too many different formats on a sheet.

Try selecting a complete sheet and changing all formats to the same colour, with the same number and font format. If that opens OK get the user to re-format the sheet, formatting by row or column, not by cell.

Being a large file, make sure the user opens it in a new instance of Excel each time and doesn't have other files open with it.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top