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

B

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
 
A

Alan Moseley

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

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
 
R

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
Click to expand...
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---------------
 
B

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
RyanG said:
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
Click to expand...
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---------------





Ron de Bruin said:
hi Barb

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

See the second macro
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

xlsm SaveAs xls 4
Excel 2007 Save as .xlsm 3
Providing suggested filename 2
Workbook does not Save from User Form 4
Convert from "xls" to "xlsm" format 7
Prompt for Save As Window through macro 7
Save as, changing default fileformat in Excel 2007 4
command button to save 1

Top