MeadInKent
| Links | Alphabetical Index | Create HTML web pages |
excel

Publish a spreadsheet as a PDF file

Excel 2010 has options to publish an Excel worksheet as a PDF file or send it as an email attachment in PDF format. No additional software or Windows printer drivers are required.

The option [File] Save and Send | Create PDF/XPS document will (by default) publish the current worksheet.

MeadInKent on YouTube

A video outlining how to publish a PDF file

Publish Excel as PDF dialog box

It is possible to publish only a certain area. Select a range of cells and choose the Create PDF option. In the Publish dialog box (above) click the [Options...] button. Choose the option for Selection and then publish.

The PDF file will inherit the current page layout / print settings. Therefore if you wish to change the PDF output to landscape/portrait or to fit it all on a single page, amend these settings by selecting: [File] Print | Settings.

Basing the PDF output on the current Print Area can make printing much easier if you repeatedly print only part of a worksheet. The Create PDF option does not inherit the Print Area range and can only be set to the currently highlighted range. Having to repeatedly select the range before creating several files may be inconvenient. A better solution is to use a macro which applies the Print Area to the PDF output.

Sub PrtPDF()
' Save the current print area as a PDF and set to fit to one page
Dim MyOptions As String, MyPrtArea As String, MyFilename As String, MyFit As Variant

MyPrtArea = ActiveSheet.PageSetup.PrintArea
MyFit = ActiveSheet.PageSetup.Zoom
MyOptions = UCase(InputBox("Print range " & MyPrtArea & " as Portrait or Landscape (or Cancel) P L C", "Print as PDF", "P"))
If MyOptions <> "C" Then

MyFilename = Application.DefaultFilePath & "\" & ActiveWorkbook.Name & ".pdf"
MyFilename = InputBox("Save PDF file as ", "Print PDF", MyFilename)

Range(MyPrtArea).Select
If MyOptions = "P" Then ActiveSheet.PageSetup.Orientation = xlPortrait
If MyOptions = "L" Then ActiveSheet.PageSetup.Orientation = xlLandscape
' The next 2 lines will force the PDF to fit the range onto one page
ActiveSheet.PageSetup.FitToPagesWide = 1
ActiveSheet.PageSetup.FitToPagesTall = 1

Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
MyFilename, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
True
Range("A1").Select
ActiveSheet.PageSetup.Zoom = MyFit ' original setting for Fit to Page
Debug.Print Time(), "PrtPDF"; MyFilename
End If

End Sub
 

A more flexible solution is to use a program which creates a PDF printer option. Free programs such as CutePDF can be downloaded and will install virtual printer drivers which send the Print output to a PDF file. These printer drivers will be available to all programs, not just Excel.

An alternative option is to use a VBA program which reads a list of worksheet names and adds all of the print areas to a single PDF file.

excel Get this information as a document
accompanied by Excel worksheets
Document is in PDF format Click here for details about obtaining this
file. It has been rewritten for Excel 2010.

file:xl_publish_pdf.htm Page last updated: Mar15 MeadInKent.co.uk 2017 CMIDX S5 P4 Y