checkcompatibility not working in Windows 7

[Charlie W]

I have an Excel macro that I use on several PCs. The macro uses the SaveAs
method to save a copy of the sheet as a separate workbook. The macro code
contains the code:

activeworkbook.checkcompatibility = false
activeworkbook.saveas ...

On PCs running XP and both Office 2003 and Office 2007 it saves the copy
without incident. On a PC running Office 2007 and Windows-7, it always
displays the Compatibility Checker warning. Also, after the SaveAs on the
Win-7 platform has executed, I notice that CheckCompatibility is set to true
again.

Any ideas how to prevent Win-7 from resetting the CheckCompatibility flag?

Thanks

 

[Ron de Bruin]

Hi Charlie

The problem is the default save format
See the example on this page and let me know if it is also working for you
http://www.rondebruin.nl/saveas.htm

 

[Charlie W]

Ron. Thanks for the quick reply. Not sure this is the
problem. The actual line of code in question is:

ActiveWorkbook.SaveAs Filename:=SaveName, _
FileFormat:=xlWorkbookNormal

In the cases I have been trying, the 'SaveName' is a full
path name of the form "C:\....filename.xls"

I wanted to use .xls file extensions because lots of folks
who receive the saved files still run office 2003. And
office 2003, even with the compatibility pack, seems to
'complain' about opening attachments saved with
'.xls' file extensions using other file formats.

I selected that xlWorkbookNormal as one format that
seemed to work best in both office 2003 and 2007.

Did I not fully understand what the explanation at the
URL you provided was saying?

 

[Ron de Bruin]

No you not understand it correct

In 2007 you must use FileFormat:=56
for a 97-2003 workbook

Try the macro below from my site to create a workbook with the activesheet and save it as
97-2003 format in your Application.DefaultFilePath (Documents folder)

Sub Save_2007_WorkSheet_As_97_2003_Workbook()
'Avoid CheckCompatibility dialog when you copy a WorkSheet
'from a 2007-2010 file with things that are new in 2007-2010
'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 = "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
End Sub

 

[Charlie W]

I get it now.

Thanks a lot.
--
cw

No you not understand it correct

In 2007 you must use FileFormat:=56
for a 97-2003 workbook

Try the macro below from my site to create a workbook with the activesheet and save it as
97-2003 format in your Application.DefaultFilePath (Documents folder)

Sub Save_2007_WorkSheet_As_97_2003_Workbook()
'Avoid CheckCompatibility dialog when you copy a WorkSheet
'from a 2007-2010 file with things that are new in 2007-2010
'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 = "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
End Sub

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm



.