Trying to add a line to vba

[pujo]

Morning all.
I am trying to add this line to the script in order for the "SaveAs" function to rename the file as the text in the quotes, and the cell valve. Thanks all.

 "Req-" & Range.("P3")

This is what I currently have. This is not my VB, I forgot where I copied it from.

Sub Save_As()
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

 

[VoG]

Try like this

file_name = "Req-" & Range("P3").Value

 

[pujo]

The only issue I have with that is there is no Save as UI. The user does not have the option to cancel.
Can this be amended so that the user can see the save as UI?

 

[VoG]

Try this

Sub Save_As()
Dim file_name As String

' Get the file name.
file_name = "Req-" & Range("P3").Value
If LCase$(Right$(file_name, 4)) <> ".xls" Then
    file_name = file_name & ".xls"
End If
If MsgBox("Are you sure you want to save as " & file_name, vbQuestion + vbYesNo) = vbNo Then Exit Sub
ActiveWorkbook.SaveAs Filename:=file_name

End Sub

 

[pujo]

Yes, that does work. The procedure defaulted to the "My Documents" folder. With the SaveAs UI, the user can change the directory and cancel.
Your vba works correct, but I need the functionality of the save as UI.
Thanks again.
Pujo

 

[VoG]

Try this - GetFolder function courtesy of Richard Schollar

Sub Save_As()
Dim file_name As String

' Get the file name.
file_name = "Req-" & Range("P3").Value
If LCase$(Right$(file_name, 4)) <> ".xls" Then
    file_name = file_name & ".xls"
End If
file_name = GetFolder("") & "\" & file_name
If MsgBox("Are you sure you want to save as " & file_name, vbQuestion + vbYesNo) = vbNo Then Exit Sub
ActiveWorkbook.SaveAs Filename:=file_name

End Sub

Function GetFolder(strPath As String) As String
Dim fldr As FileDialog
Dim sItem As String
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
    .Title = "Select a Folder"
    .AllowMultiSelect = False
    .InitialFileName = strPath
    If .Show <> -1 Then GoTo NextCode
    sItem = .SelectedItems(1)
End With
NextCode:
GetFolder = sItem
Set fldr = Nothing
End Function

 

[pujo]

Now that is nice....
It seems that when the UI is open, it is not showing the file name, it is blank.
Also, will the UI open to the previous saved directory, or is the default "My Documents"?

 

[VoG]

This should help with that but I'm not sure that we can enhance this any further

file_name = GetFolder(CurDir) & "\" & file_name