Excel 2007 Save As xlsm by default

[Zarch]

How can I set the SaveAs dialog to default to *.xlsm by default using the
method pinched from the Excel help file? i.e.

'Create file dialog object
Set FD = Application.FileDialog(MsoFileDialogType.msoFileDialogSaveAs)

'Set the default file name (& path)
FD.InitialFileName = Application.DefaultFilePath & "\Planning
Spreadsheet.xls"


I've tried:
' Specify macro enabled workbook for Excel 2007
If Application.Version >= 12 Then FD.InitialFileName =
application.DefaultFilePath & "\Planning Spreadsheet.xlsm"

I've also tried:
Application.DefaultSaveFormat = xlOpenXMLWorkbookMacroEnabled

However it always seems to revert to the *.xlsx file type.

Do I need to rewrite using the application.Dialogs(xlDialogSaveAs)
approach?

Basically I want to save as an xls file if the Excel version is 2003 and as
an xlsm version if 2007

Has anyone else noticed Excel 2007 falling over after a while if you open a
workbook containing macros from a previous version in compatibility mode?
Seems to need saving as *.xlsm to be happy.

Many thanks,

Z

 

[Ron de Bruin]

I never use FileDialog because it is not working in all Excel versions

I have a example here that use GetSaveAsFilename
See the second macro, you can remove the other items in the filter string

http://www.rondebruin.nl/saveas.htm