SaveAs - - provide option to Save As .xlsm or .xls

[Barb Reinhardt]

I have the following snippet of code

filesavename = Application.GetSaveAsFilename( _
InitialFileName:=SuggestedName & ".xls", _
FileFilter:="Excel Files (*.xls), *.xls")

How would I need to change this supply a suggestedname for either XLS or
XLSM files. When I use this, it's only .XLS I think what I'm wanting is a
File Filter that includes xls and xlsm. Can this be done? If so, I guess I
need to change the initial file name somehow.

Suggestions?

Thanks,
Barb Reinhardt

 

[Alan Moseley]

I think that your filefilter should be:-
"Excel Files (*.xls;*.xlsm), *.xls;*.xlsm"

 

[Ron de Bruin]

hi Barb

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

See the second macro

 

[Barb Reinhardt]

I believe I tried that and it didn't work. I did find the answer herer

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

Thanks,
Barb Reinhardt

 

[Barb Reinhardt]

I've FINALLY bookmarked your site. I've found a lot of useful code there.
Thanks for posting all of it.

Thanks,
Barb

 

[RyanG]

Ron,

I tried using several versions of the code that you propose, but I still
have the same recurring problem.

Here is my situation... I am doing a template for Expense Reports for
everybody in my office. Some people have office '03 installed, some have
office '07 installed. I have a macro which saves the file into a
user-specific folder when an on-screen button is clicked.

It works fine for the Office '03 users, but since I am using macros, the
users running office '07 need to save as a "Macro-Enabled Worksheet"
(*.xlsm). For these users, this causes an error box reading:

The following features cannot be save in macro-free workbooks:

VB Project

To save a file with the features, click No, and then choose a
macro-enabled file type in the File Type list.
To continue saving as a macro-free workbook, click Yes
[ YES ] [ No ] [ Help
]

Here is a section of the code that I have for the macro (Cases for several
other users were removed for simplicity). Please help me! Feel free to
email me directly at ryanjgeorge *at* gmail *dot* com

-----------Code---------------

Sub SelectSaveFileName()
'When the user clicks the button, the workbook is automatically named and
saved

Dim SaveAs As Variant

ThisFile = Range("I4").Value 'this cell contains the date information
User = Range("C6").Value 'this cell contains the user's name

Select Case User

Case "Luke" 'Luke has Office '03
SaveLoc = "W:\Customers\General\" 'Where Luke saves his files

Case "Ryan" 'Ryan has Office '07
SaveLoc = "X:\Expenses\" 'Where Ryan saves his
files

Case Else
SaveLoc = "C:\Expenses\" 'Where all other users
save their files

End Select

SaveAs = Application.GetSaveAsFilename(SaveLoc & ThisFile & ".xlsm",
filefilter:= _
" Excel Macro Free Workbook (*.xlsx), *.xlsx," & _
" Excel Macro Enabled Workbook (*.xlsm), *.xlsm," & _
" Excel 2000-2003 Workbook (*.xls), *.xls," & _
" Excel Binary Workbook (*.xlsb), *.xlsb", _
FilterIndex:=2, Title:="Choose Save Location")

If SaveAs = False Then
MsgBox User & " cancelled save"
Else
ActiveWorkbook.SaveAs Filename:=ThisFile
End If

End Sub

-----------Code---------------

 

[Barb Reinhardt]

Ryan,

If you are using this syntax in Excel 2007

ActiveWorkbook.SaveAs Filename:=ThisFile

You'll have errors when you go to open the file. You need to also define a
FileFormat which is different for each extension (.xls, .xlsx, .xlsm, etc).
Ron has provided a "cheat sheet" of sorts here

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

If you plan to save the file with the same extension, his code will work as
written for you. If, however, you want to change the file extension (.xls to
..xlsm), you'll need to figure out the file format that goes with the
extension before you save.

I generally do something like this

if Val(Application.Version) >=12 then
ActiveWorkbook.SaveAs Filename:=ThisFile, FileFormat=:myFileFormat
else
ActiveWorkbook.SaveAs Filename:=ThisFile
end if

If you have other questions, come back. And if I'm making this too
complicated, I'm sure Ron will set me straight.

HTH,

Barb Reinhardt

Ron,

I tried using several versions of the code that you propose, but I still
have the same recurring problem.

Here is my situation... I am doing a template for Expense Reports for
everybody in my office. Some people have office '03 installed, some have
office '07 installed. I have a macro which saves the file into a
user-specific folder when an on-screen button is clicked.

It works fine for the Office '03 users, but since I am using macros, the
users running office '07 need to save as a "Macro-Enabled Worksheet"
(*.xlsm). For these users, this causes an error box reading:

The following features cannot be save in macro-free workbooks:

VB Project

To save a file with the features, click No, and then choose a
macro-enabled file type in the File Type list.
To continue saving as a macro-free workbook, click Yes
[ YES ] [ No ] [ Help
]

Here is a section of the code that I have for the macro (Cases for several
other users were removed for simplicity). Please help me! Feel free to
email me directly at ryanjgeorge *at* gmail *dot* com

-----------Code---------------

Sub SelectSaveFileName()
'When the user clicks the button, the workbook is automatically named and
saved

Dim SaveAs As Variant

ThisFile = Range("I4").Value 'this cell contains the date information
User = Range("C6").Value 'this cell contains the user's name

Select Case User

Case "Luke" 'Luke has Office '03
SaveLoc = "W:\Customers\General\" 'Where Luke saves his files

Case "Ryan" 'Ryan has Office '07
SaveLoc = "X:\Expenses\" 'Where Ryan saves his
files

Case Else
SaveLoc = "C:\Expenses\" 'Where all other users
save their files

End Select

SaveAs = Application.GetSaveAsFilename(SaveLoc & ThisFile & ".xlsm",
filefilter:= _
" Excel Macro Free Workbook (*.xlsx), *.xlsx," & _
" Excel Macro Enabled Workbook (*.xlsm), *.xlsm," & _
" Excel 2000-2003 Workbook (*.xls), *.xls," & _
" Excel Binary Workbook (*.xlsb), *.xlsb", _
FilterIndex:=2, Title:="Choose Save Location")

If SaveAs = False Then
MsgBox User & " cancelled save"
Else
ActiveWorkbook.SaveAs Filename:=ThisFile
End If

End Sub

-----------Code---------------

hi Barb

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

See the second macro