Print to PDF and open Save As dialog box...

[CarlStephens]

Hello Team,

I currently have the below code that prints a sheet to pdf and saves it to the user's desktop and takes the file name from a cell and names the file, but I want to change it so that it does not save to the desktop automatically and simply opens the Save As dialog for the user to choose where to save the file. Can I kindly take a moment of someone's time to enlighten me on what change is required? Thank you in advance.

Sub PrintVisa()
Dim s As String, p As String

s = Range("N1").Value 'file name
p = Environ("USERPROFILE") & "\Desktop\"
If Right(p, 1) <> "\" Then p = p & "\"
If Dir(p, vbDirectory) = "" Then
MsgBox "The path does not exist"
Exit Sub
End If
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=p & s, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

MsgBox "Success - The file is now on your desktop, please move this file in the Visa Letters folder on One Drive by clicking cell P1. ", vbInformation
End Sub

 

[Domenic]

Try the following . . .

Sub PrintVisa()

    Dim saveAsFilename As Variant
    saveAsFilename = Application.GetSaveAsFilename( _
        InitialFileName:=Application.DefaultFilePath & "\" & Range("N1").Value, _
        FileFilter:="PDF File (*.pdf), *.pdf", _
        Title:="Save")
        
    If saveAsFilename = False Then Exit Sub

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=saveAsFilename, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    
    MsgBox "Success - The file is now located at '" & saveAsFilename & "', please move this file in the Visa Letters folder on One Drive by clicking cell P1.", vbInformation
    
End Sub

Note that you can change the default file path for InitialFilename as desired.

Hope this helps!

 

[CarlStephens]

Try the following . . .

Sub PrintVisa()

    Dim saveAsFilename As Variant
    saveAsFilename = Application.GetSaveAsFilename( _
        InitialFileName:=Application.DefaultFilePath & "\" & Range("N1").Value, _
        FileFilter:="PDF File (*.pdf), *.pdf", _
        Title:="Save")
       
    If saveAsFilename = False Then Exit Sub

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=saveAsFilename, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
   
    MsgBox "Success - The file is now located at '" & saveAsFilename & "', please move this file in the Visa Letters folder on One Drive by clicking cell P1.", vbInformation
   
End Sub

Note that you can change the default file path for InitialFilename as desired.

Hope this helps!

Worked perfectly, thank you so much.

 

[Domenic]

You're very welcome, I'm glad I could help.

Cheers!