Select Range to print in same folder as original file

[Dedeke]

Hello ,

I'm having a button which launches a macro to create a pdf file as output.

When clicking the button Is it possible to:
First select a range, which will be in the output range for my pdf file.
When range selected: choose where to save the file.
I managed the first one, but is saved in a fixed location( " C:\User... etc ) with this code:

Option expliciet
Sub Ritten_AsPDF()


Dim defined_rng As Range
Set defined_rng = Application.InputBox(Prompt:= _
"Choose the Specific Range", Title:="Microsoft Excel", Type:=8)
defined_rng.ExportAsFixedFormat Type:=0, _
Filename:="C:\Users\Dirk\Downloads\" & "Ritten", _
Quality:=0, IncludeDocProperties:=False, IgnorePrintAreas:=False, _
openafterpublish:=True


End Sub

So i am looking for a solution Filename: = the possibility to choose where to save the file

Many thx already

 

[davesexcel]

Do you mean to choose another range that has a folder name?

 

[Dedeke]

To davesexcel

No that's not the meaning.
After clicking my button i get my messagebox to select the range i need.
When i click on ok my pdf is automaticly saved in C:\users..... like defined after Filename.

So is it possible after selecting my range and clicking ok i have the choice where to save the file

This code i found let's me choose where to save but not possible to choose a range first.

Sub Ritten_AsPDF()

Dim saveLocation As String
Dim rng As Range

saveLocation = Application.GetSaveAsFilename(FileFilter:= _
         "PDF Files (*.pdf), *.pdf", Title:="PDF opslaan", _
        InitialFileName:=("Ritjes.pdf"))
Set rng = Sheets("Pdf Ritten").Range("Pdfritten")

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

So i want the combination of those two.
First select range - then a kind of classic "Save as"

Hope this make sense to you.
Uploading my complete file would be too big.

Thx already for reading

 

[davesexcel]

There is another dialog called folderpicker
Here is one thread, all sorts of threads though.

Browse for a folder in VBA

Hi, I'm having a bit of trouble getting this to work. All I want is for a dialogue box to pop up so the user can select a folder and to be able to default to the last folder they visited. I've seen lots of ways to do this but there is a problem with the best method I've found so far. I'm using...

www.mrexcel.com

Once you have a folder picked, you can combine the strings to get your filename.

 

[Dedeke]

Thx for reply but not what i am looking for.
Maybe the 2 print screens can help you out.

So the first code gives me the possibility to select a range. ( See First code.jpg)
After selecting the range and clicking on ok i would like to get the second code like (SecondCode jpeg)

For the moment , using the first code and clicking ok my pdf file is saved in C:\.... and so on.
That's the one i want to change with the possibility to save it where the user wants to save it.

 

[davesexcel]

Yes, put the folder picker after you select the range line. The code runs line by line.
-Select the range
-Select a folder
-Combine the two strings as the filename
-Export the the file as pdf

 

[Dedeke]

To all,

Playing arround with both codes and found a solution wich works fine.
Using this code now:

Sub Ritten_AsPDF()

Dim rng As Range
Dim saveLocation As String

Set rng = Application.InputBox(Prompt:= _
"Choose the Specific Range", Title:="Microsoft Excel", Type:=8)



saveLocation = Application.GetSaveAsFilename(FileFilter:= _
         "PDF Files (*.pdf), *.pdf", Title:="PDF opslaan", _
        InitialFileName:=("Ritjes.pdf"))


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