Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.GetSaveAsFilename problem
I have a userform with a button that does the following:
Private Sub PanicSwitch_Click() Dim AUserFile As Variant 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 AUserFile = Application.GetSaveAsFilename(??? ThisWorkbook.SaveAs Filename:=AUserFile End Sub I'm trying to set the InitialFilename parameter as follows: Center C&A PF Month YR Wk # analyst initials The user will select Center, Month and Wk # in the userform. We have a specific filenaming protocol so and I need to match it. Here's an example: Say John Smith is in the userform. He'll select his name as John Smith, his Center as GSO, his Month as April, his Week as Week 3. I need to somehow use this information to set up InitialFilename. Here's what it should look like: GSO C&A PF Apr 09 wk 3 js Here's another example to make it clear: Say Grace Henriques is in the form. She'll select her name as Grace Henriques, her Center as VHeathrow, her Month as December, her Week as Week 1. This is what her filename should save as: VHeathrow C&A PF Dec 09 wk1 gh I imagine the syntax will look something like this: AUserFile = Application.GetSaveAsFilename(CenterSelect & " C&A PF " & MonthRSelect & " 09 " & WeekSelect & "NameSelect", additional parameters etc) But as you can see from the file naming protocol I only need the first 3 letters of the month, I need the week to show as "wk1", "wk2" etc, and I only need the the users initials on the end. Any advice? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.GetSaveAsFilename problem
AUserFile = CenterSelect & " C&A PF " & _
MonthRSelect & " 09 " & WeekSelect & "NameSelect" ThisWorkbook.SaveAs Filename:=AUserFile If this post helps click Yes --------------- Jacob Skaria "Bishop" wrote: I have a userform with a button that does the following: Private Sub PanicSwitch_Click() Dim AUserFile As Variant 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 AUserFile = Application.GetSaveAsFilename(??? ThisWorkbook.SaveAs Filename:=AUserFile End Sub I'm trying to set the InitialFilename parameter as follows: Center C&A PF Month YR Wk # analyst initials The user will select Center, Month and Wk # in the userform. We have a specific filenaming protocol so and I need to match it. Here's an example: Say John Smith is in the userform. He'll select his name as John Smith, his Center as GSO, his Month as April, his Week as Week 3. I need to somehow use this information to set up InitialFilename. Here's what it should look like: GSO C&A PF Apr 09 wk 3 js Here's another example to make it clear: Say Grace Henriques is in the form. She'll select her name as Grace Henriques, her Center as VHeathrow, her Month as December, her Week as Week 1. This is what her filename should save as: VHeathrow C&A PF Dec 09 wk1 gh I imagine the syntax will look something like this: AUserFile = Application.GetSaveAsFilename(CenterSelect & " C&A PF " & MonthRSelect & " 09 " & WeekSelect & "NameSelect", additional parameters etc) But as you can see from the file naming protocol I only need the first 3 letters of the month, I need the week to show as "wk1", "wk2" etc, and I only need the the users initials on the end. Any advice? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.GetSaveAsFilename problem
That doesn't really address my issue. I made the following modifications to
the code (show below). This works except the only that shows up in the file name box in the Save As window is: "C&A PF 09 ". How do I make the rest of it populate? Private Sub PanicSwitch_Click() Dim AUserFile As Variant Dim FNwk As String Dim FNmonth As String Dim FNname 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 AUserFile = Application.GetSaveAsFilename(CenterSelect & "C&A PF" & FNmonth & " 09 " & FNweek & " " & FNname, "Excel Workbooks(*.xls),*.xls", , "You Must Save Before You Proceed", "Save It!") End Sub "Jacob Skaria" wrote: AUserFile = CenterSelect & " C&A PF " & _ MonthRSelect & " 09 " & WeekSelect & "NameSelect" ThisWorkbook.SaveAs Filename:=AUserFile If this post helps click Yes --------------- Jacob Skaria "Bishop" wrote: I have a userform with a button that does the following: Private Sub PanicSwitch_Click() Dim AUserFile As Variant 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 AUserFile = Application.GetSaveAsFilename(??? ThisWorkbook.SaveAs Filename:=AUserFile End Sub I'm trying to set the InitialFilename parameter as follows: Center C&A PF Month YR Wk # analyst initials The user will select Center, Month and Wk # in the userform. We have a specific filenaming protocol so and I need to match it. Here's an example: Say John Smith is in the userform. He'll select his name as John Smith, his Center as GSO, his Month as April, his Week as Week 3. I need to somehow use this information to set up InitialFilename. Here's what it should look like: GSO C&A PF Apr 09 wk 3 js Here's another example to make it clear: Say Grace Henriques is in the form. She'll select her name as Grace Henriques, her Center as VHeathrow, her Month as December, her Week as Week 1. This is what her filename should save as: VHeathrow C&A PF Dec 09 wk1 gh I imagine the syntax will look something like this: AUserFile = Application.GetSaveAsFilename(CenterSelect & " C&A PF " & MonthRSelect & " 09 " & WeekSelect & "NameSelect", additional parameters etc) But as you can see from the file naming protocol I only need the first 3 letters of the month, I need the week to show as "wk1", "wk2" etc, and I only need the the users initials on the end. Any advice? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.GetSaveAsFilename problem
I don't understand what characters are variable and which are just plain old
strings, but maybe this will give you an idea. Private Sub PanicSwitch_Click() Dim AUserFile As Variant Dim InitFileName 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 me.hide initfilename = "C:\somepath\Center C&A PF " _ & monthrselect & "YR Wk" & nameselect & ".xls" Auserfile = Application.GetSaveAsFilename(InitialFileName:=Ini tFileName, _ filefilter:="Excel files, *.xls") if auserfile = false then 'user hit cancel else ThisWorkbook.SaveAs Filename:=AUserFile end if 'still unload no matter what? unload me End Sub Is "NotSoFast" the name of the userform that owns this code? If yes, then the Me keywords (the userform that owns the code) should work ok. If no, I screwed up your intent. Bishop wrote: I have a userform with a button that does the following: Private Sub PanicSwitch_Click() Dim AUserFile As Variant 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 AUserFile = Application.GetSaveAsFilename(??? ThisWorkbook.SaveAs Filename:=AUserFile End Sub I'm trying to set the InitialFilename parameter as follows: Center C&A PF Month YR Wk # analyst initials The user will select Center, Month and Wk # in the userform. We have a specific filenaming protocol so and I need to match it. Here's an example: Say John Smith is in the userform. He'll select his name as John Smith, his Center as GSO, his Month as April, his Week as Week 3. I need to somehow use this information to set up InitialFilename. Here's what it should look like: GSO C&A PF Apr 09 wk 3 js Here's another example to make it clear: Say Grace Henriques is in the form. She'll select her name as Grace Henriques, her Center as VHeathrow, her Month as December, her Week as Week 1. This is what her filename should save as: VHeathrow C&A PF Dec 09 wk1 gh I imagine the syntax will look something like this: AUserFile = Application.GetSaveAsFilename(CenterSelect & " C&A PF " & MonthRSelect & " 09 " & WeekSelect & "NameSelect", additional parameters etc) But as you can see from the file naming protocol I only need the first 3 letters of the month, I need the week to show as "wk1", "wk2" etc, and I only need the the users initials on the end. Any advice? -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
GetSaveAsFilename | Excel Programming | |||
a question about "Application.GetSaveAsFileName" | Excel Programming | |||
Help with GetSaveAsFilename | Excel Programming | |||
GetSaveAsFileName | Excel Programming | |||
File save location with Application.GetSaveAsFilename | Excel Programming |