Compatibility Checker in Excel 2007-2010

Compatibility Checker in Excel 2007-2013

Important message to visitors of this page

Ron de Bruin decided to remove all Windows Excel content from his website for personal reasons. If you want to know why, head over to rondebruin.nl.

Luckily, Ron was kind enough to allow me to publish all of his Excel content here.

Most of these pages are slightly outdated and may contain links that don 't work. Please inform me if you find such an error and I'll try to fix it.

Kind regards

Jan Karel Pieterse

When you Save an Excel 97-2003 file(xls) that you have open in Excel 2007-2013 or use Save As to save an Excel file that is open in Excel 2007-2013 as Excel 97-2003 file (xls) it is possible that the Compatibility Checker dialog pops up.

Note: You only see this dialog if there are features in the workbook that are not supported in Excel 97-2003. The Compatibility Checker scans your workbook for Compatibility issues and displays warnings if they can be found in your workbook. These features may be lost or degraded when you continue to save the workbook in the Excel 97-2003 format.

You can click the button named "Copy to New Sheet" to copy the summary of issues on a new worksheet or read the about the issues in the dialog. In the report you can find one or both of the following two sections with every issue:

Minor loss of fidelity
If there is a Table in the workbook it is possible that you will see this error message: A Table style is applied
in this workbook. Table style formatting cannot be displayed in earlier versions of Excel.

Significant loss of functionality
Like the example in the dialog above: You will see this when you use a formula in the workbook that is added in Excel 2007 (for example SUMIFS)

Now you have information about the compatibility issues that are present in the workbook you can make a decision what to do, Save as Excel 97-2003 file or not.

Issues in the Minor loss of fidelity section

Like it says "Minor", most likely it is no problem to save the file as Excel 97-2003 file and share it with an Excel 97-2003 user. You can uncheck the checkbox named "Check compatibility when saving this workbook" if you not want to let the Compatibility Checker check your workbook anymore when you save the file.

You can change this setting in the user interface like this:
Excel 2007: Office Button>Prepare>Run Compatibility Checker
Excel 2010-2013: File>Info>Check for issues>Check Compatibility

Issues in the Significant loss of functionality section

When you have Issues in this section continue saving the workbook is most of the time not a good option because you will lose data/formulas or specific things will not work correctly anymore.
One option is not to use formulas that are new in 2007-2010. For example =SUMIFS() and =COUNTIFS() could be replaced by an equivalent =SUMPRODUCT(). See this site for more information: http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And not use columns/rows with data outside 256(IV) columns by 65536 rows. These are just two examples of issues that you can have. So looking for a workaround is maybe an option for you.

If you only want to show the data in your workbook to an Excel 97-2003 user there are two options.

1: Save your workbook as PDF file
This option is installed by default in Microsoft Office 2007 with Service Pack 2 and Office 2010-2013

2: Send your workbook in the Excel 2007-2010 format to the Excel 97-2003 user.
And let the Excel 97-2003 user install the Excel Viewer.

You can download it here:
Microsoft Excel Viewer

You can also download the Microsoft Office Compatibility Pack at the Microsoft site, with this Microsoft Office Compatibility Pack you can Open/Edit/Save files that are in the 2007-2013 format in older Excel versions, but it will not for example display columns/rows with data outside 256(IV) columns by 65536 rows. Also when there are formulas in the workbook that are new in 2007-2013 they will display a #NAME error when you recalculate the workbook. You see that the Viewer or saving as PDF is a better option if you want to show Excel 97-2003 users the data in your 2007-2013 workbook.

Compatibility Checker and VBA code

You see that in the Compatibility Checker dialog there is a checkbox named "Check compatibility when saving this workbook" to turn on or off this setting, with VBA code you can do it like this:

ActiveWorkbook.CheckCompatibility = False

Or turn it on again

ActiveWorkbook.CheckCompatibility = True

You can change this setting in the user interface like this:
Excel 2007: Office Button>Prepare>Run Compatibility Checker
Excel 2010-2013: File>Info>Check for issues>Check Compatibility

Example 1

Save one 2007-2013 worksheet as Excel 97-2003 workbook

You can use the macro below in your 2007-2013 workbook to create an Excel 97-2003 workbook of the active sheet and avoid the CheckCompatibility dialog to popup. Be sure that there are minor or no compatibility issues when you use this macro.

Sub Save_WorkSheet_As_97_2003_Workbook()
'Avoid CheckCompatibility dialog when you copy a WorkSheet
'from a 2007-2013 file with compatibility issues to a new
'workbook and save this workbook as a 97-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 it to the 97-2003 file format
    Application.DefaultSaveFormat = 56

    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 DefaultSaveFormat back to the users setting
    Application.DefaultSaveFormat = SaveFormat

    MsgBox "You can find the file in " & Application.DefaultFilePath

End Sub

I use ActiveSheet.Copy but you can also use Sheets("Sheet5").Copy
It doesn't have to be the active sheet used at that time.

Example 2

Save one 2007-2010 worksheet as Excel 97-2003 workbook and mail it

Note: The example below will create an Excel 97-2003 workbook of the Active sheet and avoid the CheckCompatibility dialog to popup and display a mail with this workbook attached if you use one of the following mail programs:

Outlook Express
Windows Mail
Windows Live Mail
Outlook

Be sure that there are minor or no compatibility issues when you use this macro.

Sub Mail_ActiveSheet_As_97_2003_Workbook()
'Working in 2007-2013
    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 it to the 97-2003 file format
    Application.DefaultSaveFormat = 56

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

    'Save/Mail the new workbook and 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 you have send
    Kill TempFilePath & TempFileName & ".xls"

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

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

Ron de BruinExcel Automation