compatibility mode checking Excel & VB

[thelemeuk]

How do I stop excel displaying a prompt to check for compatibility every time
I save a file in 97/2003 format from VB code?

 

[thelemeuk]

answer found in Excel Programming user group:

Try Application.DisplayAlerts = False
see:
http://www.microsoft.com/office/com...osoft.com/office/community/en-us/settings.xml
Thanks

 

[Jon Peltier]

I've found that this doesn't work if any changes at all have been made to
the newly created workbook prior to saving. The Compatibility Checker is not
affected by Display Alerts. You need to set an Excel 2007 property called
CheckCompatibility to False, which only affects the display of the annoying
dialog if no changes have yet been made to the workbook.

''In the main procedure:

Set wbExport = Workbooks.Add(xlWBATWorksheet)

If Val(Application.Version) < 12 Then
iFileFormat = xlWorkbookNormal
Else
iFileFormat = FormatxlExcel8
End If

If Val(Application.Version) = 12 Then
CheckCompat wbExport, False
End If

wbExport.SaveAs sFullName, ThisWorkbook.FileFormat

''Place this in a separate module, so it won't raise a compile error if
Excel 2003 or earlier is running:

Sub CheckCompat(wb As Workbook, bCheckCompat As Boolean)
wb.CheckCompatibility = bCheckCompat
End Sub


- Jon
-------
Jon Peltier, Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/>
Advanced Excel Conference - Training in Charting and Programming
http://peltiertech.com/Training/2009-06-ACNJ/AdvExcelConf200906ACNJ.html
_______

 

[thelemeuk]

Thanks Jon - the workbook/module containing the suggested code now runs OK
but as you say, the saved files still have the compatibility check when
opened and resaved.

 

[Jon Peltier]

Once the file is saved the first time, the CheckCompatibility setting works
anytime.

- Jon
-------
Jon Peltier, Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/>
Advanced Excel Conference - Training in Charting and Programming
http://peltiertech.com/Training/2009-06-ACNJ/AdvExcelConf200906ACNJ.html
_______

 

[Marie]

Hi Jon,

I created two new modules, but I'm still getting the compile error. Is
there a better way to shut the check off?

Thanks!
Marie