Excel Automation: auto confirm of Compatibility Checker notification

Question

Hi all,

a little annoying issue with Excel automation. I open a workbook from Access, make some changes (via VBA, of course), then close this book with

xlBook.Close True

Saving .xls file in Excel 2010 leads to Compatibility Checker notification.

It's ok but this message appears somewhere in background window. So the user still can see Access window and he don't understand that there is this notification. Only if you press Alt+Tab, you'll see an Excel icon (btw, it doesn't appear in task bar), you can then switch to it and confirm saving.

The question is either

How can I confirm this notification automatically?

or

How can I make it appear as an active window in front of my Access window?

Thanks in advance!

---

Andrey V Artemyev | Saint-Petersburg, Russia
Russian blog artemyev.biztoolbox.ru

Answer 1

Hi Andrey,

You may want to avoid the Compatibility Checker dialog, and please have a look at this link:

Working with the Compatibility Checker in Excel 2007 and Excel 2010

You can also perform the same actions in Microsoft Visual Basic for Applications (VBA) with either of the following statements.

VB

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.

VB

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

Good day.

---

Yoyo Jiang[MSFT]
MSDN Community Support | Feedback to us
Develop and promote your apps in Windows Store
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 2

Yoyo, thanks a lot!

.CheckCompatibility property is exactly what I was looking for. Strange, but it hasn't come to my mind that it could be just turned off. Super!

---

Andrey V Artemyev | Saint-Petersburg, Russia
Russian blog artemyev.biztoolbox.ru

Answer 3

I'm glad it helps.

---

Yoyo Jiang[MSFT]
MSDN Community Support | Feedback to us
Develop and promote your apps in Windows Store
Please remember to mark the replies as answers if they help and unmark them if they provide no help.