Application.GetSaveAsFilename Method
Displays the standard Save As dialog box and gets a file name from the user
without actually saving any files.
Syntax
expression.GetSaveAsFilename(InitialFilename, FileFilter, FilterIndex,
Title, ButtonText)
expression is a variable that represents an Application object.
Parameters:
InitialFilename: Optional Variant Specifies the suggested file name. If this
argument is omitted, Microsoft Excel uses the active workbook's name.
FileFilter: Optional Variant A string specifying file filtering criteria.
FilterIndex: Optional Variant Specifies the index number of the default file
filtering criteria, from 1 to the number of filters specified in FileFilter.
If this argument is omitted or greater than the number of filters present,
the first file filter is used.
Title: Optional Variant Specifies the title of the dialog box. If this
argument is omitted, the default title is used.
I used this method because I wanted all the spreadsheets used on my team
saved in a particular way. So I created a dialog box (userform) that prompts
a user for information: name, month, week, center they are reviewing, etc.
All of that information is captured by the dialog box, put in a particular
order and assigned to a variable I created (IFN). Then that variable is used
by the method described above to save the spreadsheet using a specified file
name. The code for the procedure follows:
Private Sub PanicSwitch_Click()
Dim AUserFile As Variant
Dim FNweek As String 'File Name
Dim FNmonth As String 'File Name
Dim FNname As String 'File Name
Dim IFN As String
Month7Select = Month7.Value
MonthRSelect = MonthR.Value
WeekSelect = Week.Value
NameSelect = AName.Value
CenterSelect = Center.Value
Cells(1, 25) = Month7Select
Cells(1, 1) = MonthRSelect
Cells(2, 1) = WeekSelect
Cells(1, 2) = NameSelect
Cells(2, 2) = CenterSelect
If MonthRSelect = "January" Then FNmonth = "Jan"
If MonthRSelect = "February" Then FNmonth = "Feb"
If MonthRSelect = "March" Then FNmonth = "Mar"
If MonthRSelect = "April" Then FNmonth = "Apr"
If MonthRSelect = "May" Then FNmonth = "May"
If MonthRSelect = "June" Then FNmonth = "Jun"
If MonthRSelect = "July" Then FNmonth = "Jul"
If MonthRSelect = "August" Then FNmonth = "Aug"
If MonthRSelect = "September" Then FNmonth = "Sep"
If MonthRSelect = "October" Then FNmonth = "Oct"
If MonthRSelect = "November" Then FNmonth = "Nov"
If MonthRSelect = "December" Then FNmonth = "Dec"
If WeekSelect = "Week 1" Then FNweek = "wk1"
If WeekSelect = "Week 2" Then FNweek = "wk2"
If WeekSelect = "Week 3" Then FNweek = "wk3"
If WeekSelect = "Week 4" Then FNweek = "wk4"
If WeekSelect = "Week 5" Then FNweek = "wk5"
If NameSelect = "Bishop Minter" Then FNname = "bm"
If NameSelect = "Carlos Trespalacios" Then FNname = "ct"
If NameSelect = "Dennis Murphy" Then FNname = "dm"
If NameSelect = "Gary Hayden" Then FNname = "gh"
If NameSelect = "Gloria Montoya" Then FNname = "gm"
If NameSelect = "Kenneth Accomando" Then FNname = "ka"
If NameSelect = "Lisa Muttillo" Then FNname = "lm"
If NameSelect = "Lorraine Warburton" Then FNname = "lw"
If NameSelect = "Warner Langlois" Then FNname = "wl"
IFN = CenterSelect & " C&A PF " & FNmonth & " 09 " & FNweek & " " & FNname
Unload NotSoFast
AUserFile = Application.GetSaveAsFilename(InitialFileName:=IFN, _
FileFilter:="Excel Macro-Enabled Workbook(*.xlsm),*.xlsm", _
FilterIndex:=1, Title:="You Must Save Before You Proceed")
ActiveWorkbook.SaveAs AUserFile, xlOpenXMLWorkbookMacroEnabled
End Sub
Keep in mind that I originally created this code in Excel 2003 so when I
migrated to 2007 the following two lines of code needed to be changed from:
AUserFile = Application.GetSaveAsFilename(InitialFileName:=IFN, _
FileFilter:="Microsoft Office Excel Workbook(*.xls),*.xls", _
FilterIndex:=1, Title:="You Must Save Before You Proceed")
ActiveWorkbook.SaveAs AUserFile, xlWorkbookNormal
to:
AUserFile = Application.GetSaveAsFilename(InitialFileName:=IFN, _
FileFilter:="Excel Macro-Enabled Workbook(*.xlsm),*.xlsm", _
FilterIndex:=1, Title:="You Must Save Before You Proceed")
ActiveWorkbook.SaveAs AUserFile, xlOpenXMLWorkbookMacroEnabled
The reason I was getting the error message and not able to open workbooks
after I saved and closed them was because I only changed the Filefilter (from
"Microsoft Office Excel Workbook(*.xls),*.xls", to "Excel Macro-Enabled
Workbook(*.xlsm),*.xlsm") and not the format. So I was saving my workbook
using a 2007 file extension (.xlsm) but using a 2003 file format
(xlWorkbookNormal). Once I changed the file format (from xlWorkbookNormal
to xlOpenXMLWorkbookMacroEnabled) from a 2003 format to a 2007 format
everything was fine.
Hope this helps.
