Microsoft Excel - Excel 2007 corrupts file when saving in *.xls compatibility mode

Asked By Warren Bain
08-Sep-09 11:59 PM

I have a client that publishes a document for their customers.  Because the customer may use any version of Excel from 97 onwards, the file is published as *.xls using Excel 97.  If the file is NOT converted then there is no problem.

However, some customers are using Excel 2007 and they are converting the file to *.xlsx native Excel 2007 format.  Later on they realise that they can only submit the file at the client website if it is in *.xls format so they File Save As *.xls.

They will recieve a message that "The document was saved successfully, but Excel cannot re-open it because of a sharing violation."  If this document is then opened by my client using Excel 2007 which is their standard, Excel reports that:

Excel found unreadable format.

Then if you click on Yes to attempt to repair the document, it works but the message received says:

Excel lost supporting data on one or more objects. One or more invalid conditional formats were removed from the workbook."

The book then appears to be OK.  However understandably, the client is confused about what has happened and a separate process which uses VBA to analyse the workbooks is unable to open the corrupted documents requiring human intervention to repair them as described.

Any clues on overcoming this apparent flaw in the Excel 2007 conversion process which doesn't appear to like going *.xls => *.xlsx => *.xls.  Or is there an MVP who can report the flaw to Microsoft?

  Gary Byrne replied to Warren Bain
17-Sep-09 09:47 PM

Hi Warren

Most likely, some part of the conditional formatting created in the 2007 version is not backward compatible.  When back-saving, be sure to check compatibility - Excel usually gives a warning that something is not compatible, but this could be one of those bugs that MSFT just hasn't gotten to yet.  If the client is not getting that warning when they save, check to see that they have the "check compatibility" option enabled.

Try this:

===================================

How to Check a workbook for compatibility with earlier versions of Excel:

To ensure that a Microsoft Office Excel 2007 workbook does not have compatibility issues that cause a significant loss of functionality or a minor loss of fidelity in an earlier version of Excel, you can run the Compatibility Checker. The Compatibility Checker finds any potential compatibility issues and helps you create a report so that you can resolve them.

 Important   When you work on a workbook in Compatibility Mode, where the workbook is in Excel 97-2003 Binary file format (BIFF8) instead of the new Office Excel 2007 XML-based file format (.xlsx), the Compatibility Checker is automatically run when you save a workbook.

  1. In Office Excel 2007, open the workbook that you want to check for compatibility.
    1. Click Microsoft Office Button Button image, and then click Open.
    2. In the Look in list, click the drive, folder, or Internet location that contains the workbook that you want to open.
    3. In the folder list, locate and open the folder that contains the workbook.
    4. Click the workbook, and then click Open.
  2. Click Microsoft Office Button Button image, click Prepare, and then click Run Compatibility Checker.
  3. To check the workbook for compatibility every time that you save it, select the Check compatibility when saving this workbook check box.
  4. To create a report in a separate worksheet of all the issues that are listed in the Summary box, click Copy to New Sheet.

     Tip   If available, you can click Fix to resolve simple issues. For more complex issues, click Help for more information.

Create New Account
help
Deleating All Selected Data Excel 2007 Excel Excel 2007 (What worked for Excel 2003 makes 2007 crash). I have run Microsoft Office Diagnostics and it can not identify any problems) When I try to delete data
Enregistrer dbf sous excel 2007 Excel Bonjour la Communaut??, Comment r??ussir ?? enregistrer un format dbf sous excel 2007? Merci pour vos conseils Bernie Excel - French Discussions Microsoft Excel (1) Excel 2003 (1) Excel 2007 (1) DBASE (1) Enregistrer (1) Communaut (1) Conseils (1
What are the differences between excel 2000 & excel 2007? Excel We're trying to decide if it would be beneficial to upgrade from excel 2000 to excel 2007. What are the differences between excel 2000 & excel 2007? Excel Miscellaneous Discussions Microsoft Excel (1) Excel 2007 (1) BA597438D5D2 (1) Previews
Conflict with Valid Range Reference Error in Microsoft Excel 2007 Excel Hi, While converting the Macros enabled Microsoft Excel 2003 to Microsoft Excel 2007 format, I am getting the following Conflict with Valid Range Reference Error. After clicking
SQL plus de Inclure les noms de champ dans SQL EXcel 2007 Excel Bonjour, Nous venons de miger de Microsoft Excel 2003 vers Microsoft Excel 2007. Dans la version Excel 2003, on avait avec clic droit sur une plage de donnÃ