Save as, changing default fileformat in Excel 2007

[Anne Schouten]

I made in Excel 2007 a template with many macros.

By default the new workbook is saved as .xlsx (without macros)

I want the default to be .xlsm (with macros), but I want to give the user
the opportunity to save it as 97-2003 workbook aswel.

I made the vba code as shown below.

The FileFilter works fine in the 'Save as' dialog window, but I do not know
how to use the chosen FileFormat.

intFileFormat is not changed by this dialogwindow. So with this code the
file is still saved as .xlsx

Who knows how to solve this problem?





Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

....

varName = Application.GetSaveAsFilename( _

fileFilter:="Excel werkmap met macro's (*.xlsm),*.xslm,Excel
werkmap (*.xlsx), *.xlsx,Excel 97-2003 (*.xls),*.xls")

If varName <> False Then

Cancel = True

intFileFormat = ActiveWorkbook.FileFormat

ActiveWorkbook.SaveAs Filename:= varName ', FileFormat:=
intFileFormat

End If

End Sub



Thanks, Anne

 

[Ron de Bruin]

This page will give you ideas
http://www.rondebruin.nl/saveas.htm

 

[Anne Schouten]

Thank you Ron, but I am afraid it is not helping me in this case.
I want the user to make the choice for the file format with the Save as
dialog window.
I could make a form and let the user choose a fileformat and use his
selection to save the file, but that will be an extra question to the user,
while with filtering the fileformat with Save as, I can let the default
fileformat be the one that is used most often (.xlsm) and still give the
user the option to save the file as 97-2003.
I cannot find this in the website you gave me.
Is there another way to program it?

Regards, Anne

 

[Ron de Bruin]

See the second example on that page

It use GetSaveAsFilename



--

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

 

[Anne Schouten]

You are right Ron. It works perfectly. Thank you very much.

Anne