VBA - Save As to Users Downloads Folder

[ElvisHess]

Hello,

I use the following Code to bring up the SAVE AS dialog box and have a custom file name appear in the File Name field.

The file I am using this with is a template used by many. What I want to do is have the code Point to the specific USERS Downloads folder, then have the user click Save and have the original Template close.

I'm having issues with the code pointing to the downloads directory as the Save As file location. I can't get it to change to even a different folder on C drive.

Sub SaveAs_Click()
Dim ThisFile As String
Sheets("CDR TEMPLATE").Select
ThisFile = Range("'CDR TEMPLATE'!H32").Value & ".xlsm"
Application.Dialogs(xlDialogSaveAs).Show ThisFile
End Sub

Please Help!!

 

[rlv01]

You have to use the environment to get the specific user's download folder path. Also, try using Application.FileDialog instead of Application.Dialog

Sub SaveAs_Click()
    Dim ThisFile As String
    Dim FolderPath As String

    FolderPath = Environ("HOMEDRIVE") & Environ("HOMEPATH") & "\Downloads"

    Sheets("CDR TEMPLATE").Select
    ThisFile = FolderPath & "\" & Range("'CDR TEMPLATE'!H32").Value & ".xlsm"

    With Application.FileDialog(msoFileDialogSaveAs)
        .AllowMultiSelect = False
        .InitialFileName = ThisFile
        .Show
    End With
End Sub