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" wrote:
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---------------
"Ron de Bruin" wrote:
hi Barb
See
http://www.rondebruin.nl/saveas.htm
See the second macro
--
Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm
"Barb Reinhardt" wrote in message
...
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