Excel VBA To Print range To PDF And Save Using Name In Cell

Brian Sowter
Brian Sowter used Ask the Experts™
on
I am looking for sample code to print a defined range in an Excel spreadsheet to a PDF and save it using name in a cell.

Any help appreciated
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2012

Commented:
Assuming you have Excel 2007+, and in the worksheet you've defined the pdf print range (PDFRange) and file to print to (pdfPathFile), you can use this code.

Sub printPDFSave()
Dim rng As Range
Dim fPathFile As String

    Set rng = [PDFRange]
    fPathFile = [pdfPathFile]
    rng.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fPathFile, quality:=xlQualityStandard, _
        includedocproperties:=True, ignoreprintareas:=True, openafterpublish:=True
    
    
End Sub

Open in new window


See attached.

Dave
printRngAsPDF-r1.xlsm
Brian SowterTechnical Director

Author

Commented:
Hello Dave
Thank you very much for this.  I get
 
"Run time error 1004.  The document was not saved ...may be open or an error was encountered."

when I click the button.  I have Excel 2007.
Regards
Brian
Most Valuable Expert 2012
Top Expert 2012

Commented:
You need perhaps to update the range L4 - to put your path and filename that tells the app where to save the PDF?

Dave
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Brian SowterTechnical Director

Author

Commented:
Sorry.  I dont understand range L4.

Why does your tested solution not run on my computer?

Brian
Most Valuable Expert 2012
Top Expert 2012
Commented:
Range L4 holds the path and filename for the PDF file.  Your original question:

>>I am looking for sample code to print a defined range in an Excel spreadsheet to a PDF and save it using name in a cell.

So, Range L4 in the active sheet holds that file path and name as you requested.  If you look at the sheet with the button on it - sheet1, the range to print has all the "test" words in it, with yellow fill.  Also with yellow fill is Range L4 which holds the path and filename of the new PDF file.  Unless you change that, you'll continue to get an error.

Here, I've updated this app to prompt you for the filename, instead:

Option Explicit

Sub printPDFSave()
Dim rng As Range
Dim fPathFile As Variant

    Set rng = [PDFRange]
    fPathFile = Application.GetSaveAsFilename(filefilter:="PDF Files (*.pdf), *.pdf")
    If fPathFile <> False Then
        rng.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fPathFile, quality:=xlQualityStandard, _
            includedocproperties:=True, ignoreprintareas:=True, openafterpublish:=True
    End If
    
    
End Sub

Open in new window


See attached.

Dave
printRngAsPDF-r2.xlsm
Brian SowterTechnical Director

Author

Commented:
Dave
brilliant...thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial