Allow user to choose the save file path for "print PDF"

Question

I have a commandbutton on the Excel file that allows the user to automate the "Print PDF" feature with a click. The issue being, at the moment I can only save the file to the specified location within the code. I want to let the user choose their save directory similar to the actual saving feature.

Here is the code I have so far:

Private Sub PrintPDF_Button_Click()

    Dim mySheets As Variant, sh

    mySheets = Array("COVER", "SCOPE", "SUMMARY", "Updated Hours EST", "RATES")

    Sheets(mySheets).Select
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\Users\sravi\test" & _
        ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, openafterpublish:=True

End Sub

Possible duplicate of [Select folder for save location](https://stackoverflow.com/questions/33411063/select-folder-for-save-location) — BigBen, Nov 28 '18 at 21:35
I am going to roll back your question to the previous state, because your edit was due to the other answer and it somewhat invalidates your accepted answer. Just to let you know why I am doing it. — K.Dᴀᴠɪs, Nov 28 '18 at 22:19

score 2 · Answer 1 · answered Nov 28 '18 at 21:37

2

Use the Application.GetSaveAsFilename method. Use the returned value as the Filename attribute, but check to make sure it is not False before saving.

answered Nov 28 '18 at 21:37

Professor Pantsless

454
4
6

score 2 · Accepted Answer · answered Nov 28 '18 at 21:54

Another method you can use is the Application.FileDialog() method. You will be able to set a default save location and even a default file name if you'd like.

Dim fileSave As FileDialog
Set fileSave = Application.FileDialog(msoFileDialogSaveAs)

With fileSave
    ' Your default save location here
    .InitialFileName = "C:\Users\sravi\*.pdf"

    If .Show = -1 Then

        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=.SelectedItems(1), _
                Quality:=xlQualityStandard, IncludeDocProperties:=True, _
                IgnorePrintAreas:=False, openafterpublish:=True

    End If
End With

Breaking it down

You can set the default save path using the .InitialFileName property. If you have a default file name, you can use that. Otherwise, set the default file type using *.pdf

.InitialFileName = "C:\Users\sravi\*.pdf"

The following line serves two purposes:

Shows the dialog box with .Show
Ensures that the user accepted a save location with .Show = -1

If .Show = -1 Then

Then finally, you would return your file path to the method you're using with this line:

Filename:=.SelectedItems(1)

Allow user to choose the save file path for "print PDF"

2 Answers2

Breaking it down