I am working on an Excel 2007 workbook that will contain a macro to save the current sheet (template) as
- PDF file (no problem)
- Excel 97-2003 file (problem)
When saving an Excel file, a message appears stating
"Certain formula names in this book can display different values when they are recounted ... Do you want Excel to recount all formulas when this workbook is open?"
Then the user can select Yes / No, and then save the file.
How to disable the message box on behalf of?
The default answer is no.
My save code :
Sub saveAs_97_2003_Workbook(tempFilePath As String, tempFileName As String)
Dim Destwb As Workbook
Dim SaveFormat As Long
'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
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