Help setting a File Name

  • Thread starter Thread starter cinnie
  • Start date Start date
C

cinnie

hi experts

I'm having trouble with the following:

Cell J1 of Sheet1 contains a date, say Dec. 15, 2009. Sheet1 has a 'Save'
button called cmbSave. When I click cbmSave, I'd like the SaveAs... dialog
to be shown with the suggested File Name showing as
'POS_Summary_091215.xls' . In other words, I'd like the suggested file name
to start with 'POS_Summary_', and to end with the J1 date in yymmdd format.
Can this be done?

thank you in advance
Cinnie
 
Sub newdate()
Dim d As Date, s As String
d = Range("J1").Value
s = Format(d, "yymmdd")
ActiveWorkbook.SaveAs Filename:="POS_Summary_" & s & ".xls"
End Sub
 
thanks Gary's Student

This indeed saves the file as requested, but is there any way to get the
Windows 'Save As' dialog box to open with the File Name box showing the name
that your code fragment produces? This way the user can change the folder if
desired.

Many thanks for any thoughts
 
Give this code a try inside your macro...

' Put this line with your other variable declarations
Dim FileNameWithPath As String, Filters As String, Index As Long
.......
.......
' Put these lines in your code wherever needed
FileNameWithPath = "c:\Temp\NewTextFilename.txt"
Filters = "All Files (*.*),*.*,Text Files (*.txt),*.txt"
Index = 2
Application.GetSaveAsFilename InitialFilename:=FileNameWithPath, _
FileFilter:=Filters, FilterIndex:=Index

Assign the filename along with its full initial path (which must exist on
the drive) to the FileNameWithPath variable (note that the specified path,
if it exists, will be what the folder tree opens to); assign a comma
delimited string (with **no** spaces around the commas) to the Filters
variable where the list is in pairs... the first item is the text that is
displayed in the "SaveAs File Type" drop down and the second item paired
with it restricts the display to the specified file extension, repeat this
pattern for the second an subsequent file specifications that you want to
predefine for the user; and assign a number corresponding the filter that
you want to be defaulted (so in my example, the 2 means use the second
filter pair as the default, which is the Text Files one).
 
Here is a more practical (and usable) example...

' Put this line with your other variable declarations
Dim FileNameWithPath As String, Filters As String
Dim SaveTo As String, Index As Long
.......
.......
' Put these lines in your code wherever needed
FileNameWithPath = "c:\Temp\RicksSaveAsTest.xls"
Filters = "All Files (*.*),*.*,Excel Files (*.xls),*.xls"
Index = 2
SaveTo = Application.GetSaveAsFilename(InitialFilename:=FileNameWithPath, _
FileFilter:=Filters, FilterIndex:=Index)
ThisWorkbook.SaveAs SaveTo

The GetSaveAsFilename dialog doesn't actually save anything, it simply gets
the filename and its path from the user, hence the last line is needed to
perform the actual save operation.
 
Back
Top