Working with the Compatibility Checker in Excel 2007 and Excel 2010

Summary:   Microsoft Office Excel 2007 and Microsoft Excel 2010 introduce the Compatibility Checker that flags issues that may cause loss of fidelity or functionality when saving workbooks to previous versions of Microsoft Excel. Learn about these options and explore associated code samples.

Applies to: Excel 2007 | Excel 2010 | Office 2007 | Office 2010 | SharePoint Server 2010 | VBA

Published:   September 2010

Provided by:  Ron de Bruin, Excel MVP | Frank Rice, Microsoft Corporation

   Watch the Video

Contents

• Compatibility Checker in Excel

• Issues with a Minor Loss of Fidelity

• Issues with the Significant Loss of Functionality

• Programmatically Working with the Compatibility Checker

• Conclusion

• Additional Resources

Compatibility Checker in Excel

To ensure that a Microsoft Office Excel 2007 or Microsoft Excel 2010 workbook does not have compatibility issues that cause a significant loss of functionality or a minor loss of fidelity in an earlier version of Microsoft Excel, you can run the Compatibility Checker.

Note

The Compatibility Checker is also available in Microsoft Office Word 2007, Microsoft Word 2010, Microsoft Office PowerPoint 2007, and Microsoft PowerPoint 2010.

The Compatibility Checker scans your workbook for issues that are not supported by earlier versions of Excel. When saving a workbook in the Excel 97 through Excel 2003 file format, Excel 2007 or Excel 2010 will scan the file and display dialog boxes to help you make decisions about compatibility issues. The Compatibility Checker can also help you create a report listing any incompatibilities and enables you to disable the feature.

More specifically, when one of the following conditions is applicable, the Compatibility Checker may display a dialog screen noting that a feature that is available in Excel 2007 or Excel 2010 is not available in the earlier format.

• You attempt to save an Excel 97 through Excel 2003 workbook (.xls) that is open in Excel 2007 or Excel 2010.

• You use Save As to save an Excel workbook that is open in Excel 2007 or Excel 2010 as the Excel 97-2003 (.xls) file format.

You can find a summary of the conditions that will be flagged by the Compatibility Checker in the article Plan for using compatibility mode in Office 2010

As stated previously, you can disable the Compatibility Checker when you save workbooks by doing one of the following actions.

To disable the Compatibility Checker

1. (Excel 2007) On the Office button, click Prepare, click Run Compatibility Checker, and then clear the Check compatibility when you save this workbook box.

2. (Excel 2010) On the File tab, click Info, click Check for issues, click Check Compatibility, and then clear the Check compatibility when you save this workbook box.

Issues with a Minor Loss of Fidelity

If there are tables in the workbook that contain style formatting, you may see the dialog shown in Figure 1 when you save the workbook to an earlier version of Excel.

Figure 1. Minor loss of fidelity dialog

In this message, the keyword is minor. When saving the Excel 2007 or Excel 2010 workbook to the earlier format, the table style formatting is removed in the saved file. This action makes the workbook compatible with the Excel 97-2003 (.xls) file format. If desired, you can disable the Compatibility Checker, save the workbook, and open it in Excel 97 through Excel 2003 without any issues.

Issues with the Significant Loss of Functionality

When you run the Compatibility Checker and there are issues in this category, you see a dialog displayed that resembles that in Figure 2.

Figure 2. Significant loss of functionality dialog

Note

Find more information about the list of issues in this category in the Microsoft Excel product team blog.

When issues in this category are encountered, it is not a good idea to save the file because you may lose data, cause certain formulas not to work, lose functionality in specific features, or all these issues.

However, in some of these cases, you may have alternate options. For example, one option is not to use formulas that are new in Excel 2007 or Excel 2010, Instead, you can use other formulas (although with possibly reduced functionality). For example, the formulas =SUMIFS() and =COUNTIFS() could be replaced by the closely equivalent =SUMPRODUCT() formula. Note that you can find more information about this formula here.

Another option is not use columns and rows with data outside the range of 256(IV) columns by 65536 rows. Instead you may want to put the additional data in a different worksheet and link to it. You may also be able to ease the issues by saving the workbook as a PDF file to view in previous versions of Excel. This option is installed by default in Microsoft Office 2010 and in 2007 Microsoft Office system Service Pack 2 (SP2).

Note

If you do not want to install 2007 Microsoft Office system SP2, you can install just the add-in that is available at 2007 Microsoft Office Add-in: Microsoft Save as PDF.

You can also send the Excel 2007 or Excel 2010 workbook to the user and have he or she down load and install the Excel Viewer. The Excel Viewer enables you to open, view, and print Excel 97 through Excel 2010 workbooks, even if you do not have Excel installed.

And finally, a less than optimal solution is to download the Microsoft Office Compatibility Pack for Word, Excel, and PowerPoint File Formats. By using this download, you can open, edit and save workbooks that are in the Excel 2007 and Excel 2010 file format in previous versions of Excel. However, limitations include being unable to display columns and rows with data outside the 256(IV) columns by 65536 rows boundaries. Additionally, when there are formulas in the workbook that are new to Excel 2007 or Excel 2010, you will get a #NAME error when you recalculate the workbook.

Programmatically Working with the Compatibility Checker

Previously, you saw that in the Compatibility Checker dialog there is a box titled Check compatibility when you save this workbook that enables you to disable or enable the feature. You can also perform the same actions in Microsoft Visual Basic for Applications (VBA) with either of the following statements.

ActiveWorkbook.CheckCompatibility = False
ActiveWorkbook.CheckCompatibility = True

You can use the following VBA subroutine to save an Excel 2007 or Excel 2010 worksheet as an Excel 97 through Excel 2003 workbook and avoid the Compatibility Checker dialog. However, you will want to ensure that the worksheet contains no or just minor compatibility issues.

Sub Save_2007_WorkSheet_As_97_2003_Workbook()
' Avoid the CheckCompatibility dialog when you copy a worksheet
' from an Excel 2007 or Excel 2010 file format with compatibility issues to a new
' workbook and save this workbook as an Excel 97 through Excel 2003 workbook.
    Dim Destwb As Workbook
    Dim SaveFormat As Long
    Dim TempFilePath As String
    Dim TempFileName As String

    ' Remember the users setting.
    SaveFormat = Application.DefaultSaveFormat
    ' Set the default format to the Excel 97 through Excel 2003 file format.
    Application.DefaultSaveFormat = 56

' You can specify a worksheet other than the active sheet by
' using the following syntax: Sheets("Sheet5").Copy.
    ActiveSheet.Copy
    Set Destwb = ActiveWorkbook
    Destwb.CheckCompatibility = False

    ' Save the new workbook and close it.
    TempFilePath = Application.DefaultFilePath & "\"
    TempFileName = "Excel 97-2003 WorkBook " & Format(Now, "yyyy-mm-dd hh-mm-ss")

    With Destwb
        .SaveAs TempFilePath & TempFileName & ".xls", FileFormat:=56
        .Close SaveChanges:=False
    End With

    ' Set default save format back to the users setting.
    Application.DefaultSaveFormat = SaveFormat
    
    MsgBox "You can find the file in " & Application.DefaultFilePath
    
End Sub

You can use the following subroutine to save an Excel 2007 or Excel 2010 worksheet as an Excel 97 through Excel 2003 workbook and avoid the Compatibility Checker dialog, and then add it as an attachment to an email. You will want to ensure that the worksheet contains no or just minor compatibility issues.

Note

You can use this subroutine with the following programs:

• Microsoft Outlook Express

• Microsoft Windows Mail

• Microsoft Windows Live Mail

• Microsoft Outlook

Sub Mail_ActiveSheet_As_97_2003_Workbook()
' Works with Excel 2007 and Excel 2010.
    Dim SaveFormat As Long
    Dim FileExtStr As String
    Dim FileFormatNum As Long
    Dim Sourcewb As Workbook
    Dim Destwb As Workbook
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim I As Long

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    Set Sourcewb = ActiveWorkbook

    ' Remember the users setting.
    SaveFormat = Application.DefaultSaveFormat
    ' Set the default to the Excel 97 through Excel 2003 file format.
    Application.DefaultSaveFormat = 56

    ActiveSheet.Copy
    Set Destwb = ActiveWorkbook
    Destwb.CheckCompatibility = False

    ' Save and mail the new workbook and then close it.
    TempFilePath = Application.DefaultFilePath & "\"
    TempFileName = "Part of " & Sourcewb.Name & " " _
                 & Format(Now, "yyyy-mm-dd hh-mm-ss")

    With Destwb
        .SaveAs TempFilePath & TempFileName & ".xls", _
                FileFormat:=56
        On Error Resume Next
        For I = 1 To 3
            .SendMail "", _
                      "This is the Subject line"
            If Err.Number = 0 Then Exit For
        Next I
        On Error GoTo 0
        .Close SaveChanges:=False
    End With

    ' Delete the file that you have just sent.
    Kill TempFilePath & TempFileName & ".xls"

    ' Set default save format back to the users setting.
    Application.DefaultSaveFormat = SaveFormat

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub

You can find more mailing examples and tips on how to change the code here.

Conclusion

When saving Excel 2007 or Excel 2010 files to previous Excel version file formats, you can experience compatibility issues that can cause the loss if file fidelity or significant loss of data and functionality. The Compatibility Checker can help you lessen these issues.

Additional Resources

Find more information about the topics discussed in this article at the following locations:

• Check a workbook for compatibility with earlier versions of Excel

• The Ken Puls blog

• Use Office Excel 2007 with earlier versions of Excel