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.
"Spurfan" wrote:
> I'm sorry Bishop, but you are way over my head. Can you simplify a little?
>
> Thanks.
>
>
>
> "Bishop" wrote:
>
> > I created that code myself to utilize the GetSaveAsFileName procedure. I was
> > getting the error message because I was assigning the wrong file format to
> > match the filefilter setting.
> >
> > "Spurfan" wrote:
> >
> > > Where is this code located?
> > >
> > > Thanks.
> > >
> > >
> > > "Bishop" wrote:
> > >
> > > > Ok, turns out the file format has to match the filefilter set so once I set
> > > > the file format to the correct setting problem solved. Here's what my code
> > > > looks like now:
> > > >
> > > > 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
> > > >
> > > > This are the main formats in Excel 2007 :
> > > >
> > > > 51 = xlOpenXMLWorkbook (without macro's in 2007, xlsx)
> > > > 52 = xlOpenXMLWorkbookMacroEnabled (with or without macro's in 2007, xlsm)
> > > > 50 = xlExcel12 (Excel Binary Workbook in 2007 with or without macro’s, xlsb)
> > > > 56 = xlExcel8 (97-2003 format in Excel 2007, xls)
> > > >
> > > > so just pick whichever applies
> > > >
> > > > Cheers
> > > >
> > > > "Bishop" wrote:
> > > >
> > > > > I had the following code for a 2003 Excel Spreadsheet:
> > > > >
> > > > > 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
> > > > >
> > > > > We have upgraded to Excel 2007 and now everytime I open this workbook and it
> > > > > saves using the above code I can't re-open it! I've changed the code for the
> > > > > AUserFile but I don't know how to change the second line of code to make it
> > > > > work. The changes I made are below:
> > > > >
> > > > > AUserFile = Application.GetSaveAsFilename(InitialFileName:=IFN, _
> > > > > FileFilter:="Excel Macro-Enabled Workbook(*.xlsm),*.xlsm", _
> > > > > FilterIndex:=1, Title:="You Must Save Before You Proceed")
> > > > >
> > > > > ActiveWorkbook.SaveAs AUserFile, xlWorkbookNormal
> > > > >
> > > > > I'm thinking xlWorkbookNormal has to be changed but I don't know to what.
Similar Threads
