none
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

    Wednesday, February 27, 2013 9:24 AM

Answers

  • 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.

    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

    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.

    Thursday, February 28, 2013 6:09 AM
    Moderator

All replies

  • 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.

    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

    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.

    Thursday, February 28, 2013 6:09 AM
    Moderator
  • 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

    Thursday, February 28, 2013 6:48 AM
  • 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.

    Thursday, February 28, 2013 7:27 AM
    Moderator