Export specific range to pdf and let user choose where to safe

[Dedeke]

Hello there,
For the moment i use the code below to export a certain range from a sheet as pdf file in a folder "C:\Test.
This works fine but now i would like the possibility that the user can choose where he wants to save the file and with wich name.
Kind off like the basic "Save As" in Office.

It is import that i can keep the range. So user does not have too change the range. For the moment it is a simple button to activate the sub.

'Sub ExportKlPilAsPDF()

'Create and assign variables
Dim saveLocation As String
Dim rng As Range

saveLocation = "C:\Test\myPDFFile.pdf"
Set rng = Sheets("Klassementen").Range("B1:G37")

'Save a range as PDF
rng.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=saveLocation
End Sub

Using Office 2016

All suggestions, solutions are appreciated.

Thx already

 

[AC PORTA VIA]

Try this- caution it will overwrite any pdf files with same name if exist already in the destination folder

Sub ExportKlPilAsPDF
Dim sFolder As String
Dim myAns As String
Dim rng As Range

myAns = InputBox(prompt:="Please enter file name ")


With Application.FileDialog(msoFileDialogFolderPicker)
        If .Show = -1 Then
            sFolder = .SelectedItems(1)
        End If
End With

If sFolder <> "" Then

Set rng = Sheets("Klassementen").Range("B1:G37")

    rng.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=sFolder & "\" & myAns, _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, _
    IgnorePrintAreas:=False ',OpenAfterPublish:=True

   End If
End Sub

 

[Dedeke]

@ AC PORTA VIA,

Thx for input. This is a working solution. Is there a possibilyty , instead of the MsgBox going directly to the Aplication.File dialog? Something like in the included picture. Most users are used to work this way to safe a file.

But already apreciating a solution

 

[AC PORTA VIA]

not sure it is possible like that- only if the name of pdf file is hard codded inside macro but you said you want to choose folder and name it

 

[Dedeke]

Maybe misunderstanding, played around with all possible codes i could find on the subject.
I've got it working. Like this it works like any other "Save As" file.
But anyhow lots of Thx , you helped me into the right direction.

Sub KlPil_NatAsPDF()

Dim saveLocation As String
Dim rng As Range

saveLocation = Application.GetSaveAsFilename(FileFilter:= _
         "PDF Files (*.pdf), *.pdf", Title:="PDF opslaan", _
        InitialFileName:=("KlasPilNat.pdf"))
Set rng = Sheets("Klassementen").Range("B1:G37")

'Save a range as PDF
rng.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=saveLocation
End Sub

 

[AC PORTA VIA]

yes misunderstanding but glad you got it sorted