PDF Create

[ashani]

Hi gurus,

Is it possible for user to create PDF through excel and when click on the PDF macro button - the pop-up box appears to ask which sheets need to be saved as PDF and then location window to ask where to save ?

many thanks,

 

[kweaver]

This is a pretty nice macro, courtesy of TrumpExcel.com ... modify for your needs. Will print your selection to a PDF file and give you the option to change the name and path.

Sub PrintSelectionToPDF()
'SUBROUTINE: PrintSelectionToPDF
'DEVELOPER: Ryan Wells
'DESCRIPTION: Print your currently selected range to a PDF

Dim ThisRng As Range
Dim strfile As String
Dim myfile As Variant

If Selection.Count = 1 Then
Set ThisRng = Application.InputBox("Select a range", "Get Range", Type:=8)
Else
Set ThisRng = Selection
End If
'Prompt for save location
strfile = "Selection" & "_" _
& Format(Now(), "yyyymmdd_hhmmss") _
& ".pdf"
strfile = ThisWorkbook.Path & "\" & strfile

myfile = Application.GetSaveAsFilename _
(InitialFileName:=strfile, _
FileFilter:="PDF Files (*.pdf), *.pdf", _
Title:="Select Folder and File Name to Save as PDF")

If myfile <> "False" Then 'save as PDF
ThisRng.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
myfile, Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=True
Else
MsgBox "No File Selected. PDF will not be saved", vbOKOnly, "No File Selected"
End If

End Sub

 

[ashani]

Hi
Thank you for the prompt reply.

This macro asking me to select range - however I'm looking for a message box pops up with sheet names in it - user select the sheets and click ok to move onto next step to save as PDF.

Do you think is it possible ?

Thanks once again.

 

[kweaver]

If you select (using CTRL, click) the sheets you want to save as a single PDF file, this VBA will walk you through it:

Sub SaveSelectedSheetsToPDF()
Dim str As String, myfolder As String, myfile As String

str = "Do you want to save these sheets to a single pdf file?" & Chr(10)
For Each sht In ActiveWindow.SelectedSheets
str = str & sht.Name & Chr(10)
Next sht

answer = MsgBox(str, vbYesNo, "Continue with save?")
If answer = vbNo Then Exit Sub

With Application.FileDialog(msoFileDialogFolderPicker)
.Show
myfolder = .SelectedItems(1) & "\"
End With

myfile = InputBox("Enter filename", "Save as..")

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
myfolder & myfile _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=True

End Sub

OR, here's a quick way to save the sheet(s) you've selected without all the prompting/help messages.
Is this what you mean?

Sub SaveActiveSheetsAsPDF()

Sub SaveActiveSheetsAsPDF()

Dim saveLocation As String
saveLocation = "C:\Users\etc\FileName.pdf"

'Save Active Sheet(s) as PDF
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:=saveLocation

End Sub

A message box with sheet names could be a huge issue if you have hundreds of sheets, for example! Phew.

 

[ashani]

Hi @kweaver

Thank you once again. Similar sort of thing but let me explain what I'm looking for in detail.

So I have a spreadsheet with 8 sheets there

- Sheet 1
- Sheet 2
- Sheet 3
- Sheet 4
- Sheet 5
- Sheet 6
- Sheet 7
- Sheet 8

on everysheet - there's a macro button called "Save as PDF".

What I want is when a user click that button from any of the above 8 sheets then

- A message box pops up with all the above sheets there - user can select which multiple sheets from the message box they want to save as PDF
- Once they selected sheets and click ok
- Than it will ask for the location where to save

Thank you once again for all your help.

 

[ashani]

is it at all possible to do ?
Thanks

 

[kweaver]

Personally, I'm not sure. I think you'll need to create a ListBox and adapt that, but I don't have the code to create that for you. Maybe someone else will jump in.
I hoped that simply selecting the sheets would work to print those you or your users would want.

 

[John_w]

Hi @kweaver

Thank you once again. Similar sort of thing but let me explain what I'm looking for in detail.

So I have a spreadsheet with 8 sheets there

- Sheet 1
- Sheet 2
- Sheet 3
- Sheet 4
- Sheet 5
- Sheet 6
- Sheet 7
- Sheet 8

on everysheet - there's a macro button called "Save as PDF".

What I want is when a user click that button from any of the above 8 sheets then

- A message box pops up with all the above sheets there - user can select which multiple sheets from the message box they want to save as PDF
- Once they selected sheets and click ok
- Than it will ask for the location where to save

Thank you once again for all your help.

See the following thread which uses the DialogSheet object to display a list of sheet names and checkboxes, allowing the user to select the sheets to save as PDF:

Printing selected sheets as PDF

Hello Everyone! I regularly follow the threads on this forum and I has found many times great solutions! But now I need a liitle help. I found a perfect "Walkenbach-solution" for my problem. I have a workbook with many sheets. I want to select sheet using a selectbox (VBA) an these sheet must be...

www.mrexcel.com

You just need to replace the hard-coded PDF file name with either Application.GetSaveAsFilename or Application.FileDialog(msoFileDialogSaveAs) to prompt the user for the folder and file name to save the PDF.

 

[kweaver]

ashani: I ran John_w's routine and I think it does what you want. I used a sample file with 50 sheets and it listed them all in the window and enabled checking what I wanted/needed.

 

[ashani]

Thanks you everyone - really appreciate your guidance.