PDA

View Full Version : Solved: Default file name in Save As dialog



Whitty
08-07-2007, 02:07 PM
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:

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

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:

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

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
08-07-2007, 03:02 PM
Hi Whitty,
Welcome to VBAX,
Add the filename at the start of the procedure
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

Bob Phillips
08-07-2007, 03:18 PM
I would use the folderpicker dialog to get the folder, otherwise they can change the filename



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

mdmackillop
08-07-2007, 03:30 PM
BTW,
Please use a meaningful title for your questions. It permits searches and is more likely to get appropriate attention.

Whitty
08-07-2007, 07:20 PM
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
08-07-2007, 11:40 PM
Glad to help. If your question is answered, you can mark it "solved" using the Thread Tools dropdown.