Excel VBA - How to prompt user to save wkbook w/ specific name & location

This is the code I tried but can't get to work:

sFileSaveName=ThisWorkbook.Range("rFileName").value

With Application.Dialogs(xlDialogSaveAs)("C:\RootFolder\Subdir\" & sFileSaveName)
    .Show
End With

Thanks, --Andres
AndresHernandoAsked:
Who is Participating?
 
nutschCommented:
you can try something like:

thisworkbook.saveas application.getsaveasfilename
0
 
andrewssd3Commented:
Alternatively:
    Dim dlg As Office.FileDialog
    Dim sFileSaveName As String
    
    sFileSaveName = Application.Range("rFileName").Value


    Set dlg = Application.FileDialog(msoFileDialogSaveAs)
        
    With dlg
        .InitialFileName = "C:\RootFolder\Subdir\" & sFileSaveName
        
        If .Show Then
            .Execute
        End If
    End With

Open in new window

0
 
Rob HensonFinance AnalystCommented:
Rather than showing the File Save As dialog with a default entry, can you not just save the file with the required path & name?

This code will do that.

sFileSaveName = Range("File_Name")
    ActiveWorkbook.SaveAs Filename:=sFileSaveName _
        , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False

Open in new window


Using the Save As dialog opens up the option for user to use a different name. Do you want this?

Thanks
Rob H
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.