PDA

View Full Version : Solved: use a cell reference to name a save-as



mperrah
06-21-2007, 08:01 PM
I have a project that I need to save parts of and email them to three different people.
Each person only needs some the sheets in the project.
I run a macro to remove the unwanted sheets for each person
and I have 3 named cells that have the names I want to use to save as.
When I use the recorder, it won't let me paste form the clipboard.
I found a code in this forum to start the save as dialoge,
is there a way to script the 'save as' to use a named range (or cell)?

one named cell is "wprName" for one of the projects
the location is AK3

this is the code I have started:
not sure where to code the named range for the file save as name...

Sub SaveAsName()
Dim sFileName As String
'Show the open dialog and parse the selected _
file name To the String variable "sFileName"
sFileName = Application.GetSaveAsFilename
'They have cancelled
If sFileName = "False" Then
Exit Sub
ThisWorkbook.SaveAs sFileName
End If ' add later to fix error
End Sub
thank you in advance
Mark

mikerickson
06-21-2007, 08:09 PM
This will set the default file name in the GetSaveAsFilename to the value in the cell.
Sub SaveAsName()
Dim sFileName As String
'Show the open dialog and parse the selected _
file name To the String variable "sFileName"
sFileName = Application.GetSaveAsFilename(InitialFilename:=Range("AK3").Value)
'They have cancelled
If sFileName = "False" Then
Exit Sub
ThisWorkbook.SaveAs sFileName
End Sub

mperrah
06-21-2007, 10:17 PM
Your code works great!
Thank you.

One more thing, the cell has a date code, I need it in mmddyy format,
but it is a concantination of several elements.
I pasted the vba below.
OfficeName is a named cell
WkStart is a named cell,
I add 7 to get the day the report is due
Any suggestions?

="trainingqc_" & _
OfficeName & "_" & _
MONTH(WkStart+7) & _ 'this give a single digit (6), need two digit (06)
DAY(WkStart+7) & _
YEAR(WkStart+7)

geekgirlau
06-21-2007, 10:47 PM
="trainingqc_" & _
OfficeName & "_" & _
Format(WkStart+7,"mmddyy")

mperrah
06-21-2007, 11:31 PM
Love your icon by the way, my son just turned 2 in April

I was ahead of myself, the code you modified is a formula in a cell not vba
I tried pasting your code into the cell and it says #name?

Is there a way to format the dates in excell as formulas to show mm/dd/yy
without the "/" in between (mmddyy)

the end result I'm working towards is:
trainingqc_modesto_062507.xls for the file name I'm running the macro to save as
the trainingqc and modesto part wont ever change, but the date code is different every week
The date is the day the report is due, but the file is generated before that.

One cell in the report is last mondays date (named WkStart)
and adding 7 is the report due date (WkStart + 7)
I am then trying to delete a few sheets not needed for the report
and naming the 'save as' file using a macro
and cell formulated with the current name to be used.
This report will be generated by 20 coworkers with their own copy of the file
and I am trying to automate the process so every report gets submitted with the same naming scheme.
Some of the workers have, how do you put it being politacaly correct...
have weight challenged fingers?
Thanks in advance.
Mark

mperrah
06-22-2007, 12:03 AM
I found it. I tried TEXT() before, but was omitting the double qoutes, Duh!

=qctype&OfficeName& TEXT(F9, "mmddyy") ' cell formula

Thanks again for your help.

Mark