Solved: Default file name in Save As dialog

[Whitty]

I have created a user form in Excel. I added a button that will invoke the "Save As" dialog. I want to change the default file name to the contents of a particular cell plus the word "Original". Can this be done? I know it can be done without going through the Save As window, but we ned to change the location where the file will be saved each time so that is why I need the dialog box.

Here is the code I am using to invoke the Save As function:

[VBA] Private Sub CommandButton1_Click()
Dim file_name As Variant
' Get the file name.
file_name = Application.GetSaveAsFilename( _
FileFilter:="Excel Files,*.xls,All Files,*.*", _
Title:="Save As File Name")
' See if the user canceled.
If file_name = False Then Exit Sub
' Save the file with the new name.
If LCase$(Right$(file_name, 4)) <> ".xls" Then
file_name = file_name & ".xls"
End If
ActiveWorkbook.SaveAs Filename:=file_name
End Sub
[/VBA]
Here is the code that will save the file with the file name as I want it but it does not go through the Save As dialog box:

[VBA] Private Sub CommandButton3_Click()
Dim fName As String
fName = Range("A1").Value & " Original"
' Change directory to suit your PC, including USER NAME
ChDir _
"C:\"
ActiveWorkbook.SaveAs Filename:=fName
End Sub[/VBA]

Somehow the functionality of the second needs to be added to the first. I have been unsuccessful trying to do just that.

Thanks in advance for the help!

[mdmackillop]

Hi Whitty,
Welcome to VBAX,
Add the filename at the start of the procedure
[VBA]Private Sub CommandButton1_Click()
Dim file_name As Variant
Dim FName As String
FName = Range("A1").Value & " Original"
' Get the file name.
file_name = Application.GetSaveAsFilename(FName, _
FileFilter:="Excel Files,*.xls,All Files,*.*", _
Title:="Save As File Name")
' See if the user canceled.
If file_name = False Then Exit Sub
' Save the file with the new name.
If LCase$(Right$(file_name, 4)) <> ".xls" Then
file_name = file_name & ".xls"
End If
ActiveWorkbook.SaveAs Filename:=file_name
End Sub
[/VBA]

[Bob Phillips]

I would use the folderpicker dialog to get the folder, otherwise they can change the filename

[vba]

Private Sub CommandButton1_Click()
Dim file_name As Variant
Dim FName As String
FName = Range("A1").Text & " Original"
' Get the file name.
With Application.FileDialog(msoFileDialogFolderPicker)
.Show

If .SelectedItems.Count = 1 Then
file_name = .SelectedItems(1) & Application.PathSeparator & FName & ".xls"
ActiveWorkbook.SaveAs Filename:=file_name
End If
End With
End Sub
[/vba]

[mdmackillop]

BTW,
Please use a meaningful title for your questions. It permits searches and is more likely to get appropriate attention.

[Whitty]

Thanks MD for the code. It works like a champ! I also appreciate the gentle delivery of the forum etiquette tip. This was my first forum post. I'm sure I will have more. Thanks!!

xld - I tried your code as well and I see your approach. That could work too, but I like just having the default file name changed and ready to be saved where I need to put the file. Thanks as well!!

[mdmackillop]

Glad to help. If your question is answered, you can mark it "solved" using the Thread Tools dropdown.