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

Dedeke

New Member
Joined
Dec 1, 2020
Messages
24
Office Version
  1. 2016
Platform
  1. Windows
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.

VBA Code:
'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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

AC PORTA VIA

Board Regular
Joined
Apr 9, 2016
Messages
89
Office Version
  1. 365
Platform
  1. Windows
Try this- caution it will overwrite any pdf files with same name if exist already in the destination folder
VBA Code:
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

New Member
Joined
Dec 1, 2020
Messages
24
Office Version
  1. 2016
Platform
  1. Windows
@ 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
 

Attachments

  • Save As.JPG
    Save As.JPG
    37.9 KB · Views: 3

AC PORTA VIA

Board Regular
Joined
Apr 9, 2016
Messages
89
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Dec 1, 2020
Messages
24
Office Version
  1. 2016
Platform
  1. Windows
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.

VBA Code:
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
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,130,174
Messages
5,640,601
Members
417,156
Latest member
Ciupanezul21

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top