Application.GetSaveAsFilename question

B

Bishop

Why can't I get this to work? I'm trying to use GetSaveAsFilename to prompt
the user to save a file using a specific file naming protocol. Examples of
what the filename should look like:

GSO C&A PF Jul 09 wk1 bm
VHeathrow C&A PF Dec 09 wk5 lw
Austin C&A PF May 09 wk2 gh

I'm using IFN to set up what the filename should look like then trying to
set InitialFilename parameter equal to IFN but all I keep getting is "C&A PF
09 ". So basically it's picking up everything between ""'s and excluding the
variables. How do I get the variables in the assignment?
Private Sub PanicSwitch_Click()
Dim AUserFile As Variant
Dim FNwk As String
Dim FNmonth As String
Dim FNname As String
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
Unload NotSoFast

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

AUserFile = Application.GetSaveAsFilename(InitialFileName:=IFN,
FileFilter:="Excel Workbooks(*.xls),*.xls", FilterIndex:=1, Title:="You Must
Save Before You Proceed")
End Sub
 
D

Dave Peterson

I don't see where fnmonth is set.

IFN = CenterSelect & "C&A PF" & FNmonth & " 09 " & FNweek & " " & FNname

And FNweek/FNWk looks like a problem, too.
 
J

Jacob Skaria

Dear Bishop

When you assign a value to a string variable it should be within double quotes

Should be FNmonth = "Jan"
and NOT FNmonth = Jan

I would suggest using Option Explicit on top of the module to notify such
errors.


If this post helps click Yes
 
B

Bishop

FNmonth is defined in the group of If Then statements:

If MonthRSelect = "January" Then FNmonth = Jan
etc

Good catch on the FNwk. I missed that.
 
B

Bishop

Ok, I added quotes as you suggested. I stepped through the code line by line
and for some reason my If Then statements aren't assigning values to my
variables. After stepping through, when I get to the bottom of the If Then
statement block none of the variables have assigned values. The all read
="". Why aren't they working?
 
D

Dave Peterson

What's Jan?

Did you mean "Jan"?
FNmonth is defined in the group of If Then statements:

If MonthRSelect = "January" Then FNmonth = Jan
etc

Good catch on the FNwk. I missed that.
 
B

Bishop

Ok, figured it out. The "Unload NotSoFast" command was erasing all of my
variables before they could be reassigned via the If Then statements to be
used in the IFN variable. I moved the Unload NotSoFast command below the If
Then block and now it works as intended. I've included the final code below:

Private Sub PanicSwitch_Click()
Dim AUserFile As Variant
Dim FNweek As String
Dim FNmonth As String
Dim FNname As String
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 Workbooks(*.xls),*.xls", FilterIndex:=1, Title:="You Must
Save Before You Proceed")
End Sub
 

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

Top