In this post, we’ll walk through the process of saving one or more Excel worksheets as a single PDF document using VBA. This is a common task carried out by analysts of all types, and the code that follows will demonstrate how to go about accomplishing it.

In addition, we’ll cover some of the many formatting and configuration options exposed by the PageSetup property of an Excel worksheet.

The logic for saving to PDF the worksheets contained within an Excel workbook is straightforward: To collate and export all worksheets in a workbook to PDF, pass a filepath to ExportAsFixedFormat‘s Filename parameter (for this example, we’ll use C:\Documents\pdf_from_xlsx.pdf), and set the Type and Quality parameters as follows:

ThisWorkbook.Worksheets.Select
ActiveSheet.ExportAsFixedFormat _ 
              Type:=xlTypePDF, _
              Filename:="C:\Documents\pdf_from_xlsx.pdf", _
              Quality:=xlQualityStandard

If the task at hand necessitates exporting a subset of worksheets within a workbook, pass the worksheet names to the Array function (be sure to surround the sheet names with quotes), then call the Select method of Array in place of Worksheets in the code above. For example, to save only Sheet1, Sheet2 and Sheet3 from a workbook, execute the following:

ThisWorkbook.WorkSheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
ActiveSheet.ExportAsFixedFormat _ 
              Type:=xlTypePDF, _
              Filename:="C:\Documents\pdf_from_xlsx.pdf", _
              Quality:=xlQualityStandard

After running, a PDF document pdf_from_xlsx.pdf will be output to the C:\Documents directory.

Page Setup Options

The full list of properties associated with PageSetup can be found here. Most are self-explanatory, but a few take non-obvious arguments, or the arguments need to be passed in a non-standard way. We’ll start with the more typical options, while working some of the non-standard properties in along the way.

We can apply our PageSetup options to each worksheet in turn iteratively. Naturally, it’s important to carry out any PageSetup specifications prior to calling ExportAsFixedFormat. The following example iterates over each worksheet in the current workbook, applying the same formatting to each:

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets

    With ws.PageSetup

        .PrintArea = ws.Range("A1").CurrentRegion.Address
        .CenterHorizontally = True
        .CenterVertically = False
        .PrintGridlines = False
        .Orientation = xlPortrait
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = False
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.6)
        .LeftMargin = Application.InchesToPoints(0.4)
        .RightMargin = Application.InchesToPoints(0.4)
        .BottomMargin = Application.InchesToPoints(1.2)
        .TopMargin = Application.InchesToPoints(1.0)

    End With

Next ws

PrintArea specifies the range to export from each worksheet. In this example, the continuous range starting in Cell A1 for each worksheet is highlighted.

PrintGridlines, CenterHorizontally and CenterVertically each take a Boolean value which controls the PageSetup property corresponding to its name.

Orientation can be set to one of xlPortrait or xlLandscape.

Zoom can be bound to any number greater than 0 or False. If set to False, the FitToPagesWide and FitToPagesTall properties control worksheet scaling. If Zoom is not False, FitToPagesWide and FitToPagesTall are ignored. Unless custom scaling is necessary, setting Zoom to False, FitToPagesTall to False and FitToPagesWide to 1 are satisfactory defaults for many cases.

HeaderMargin and FooterMargin specify the width of the header and footer margins, respectively, in points (1 inch = 72 points). Use the InchesToPoints method to convert the desired padding in inches to points.

TopMargin and BottomMargin specify the padding for data exported to PDF. If TopMargin, BottomMargin, HeaderMargin or FooterMargin are bound to anything other than default values, ensure BottomMargin is greater than FooterMargin, and HeaderMargin is less than TopMargin, otherwise the potential exists for the data to overwrite header/footer annotations.

Building on the PageSetup construct above, the next block writes page number to the center footer, places the current date in the left footer and the current time in the right header. In addition, for worksheets containing many rows, it’s helpful to include the header row for each page written to PDF. This is accomplished with PrintTitleRows:

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets

    With ws.PageSetup

        .PrintArea = ws.Range("A1").CurrentRegion.Address
        .CenterHorizontally = True
        .CenterVertically = False
        .PrintGridlines = False
        .Orientation = xlPortrait
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = False
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.6)
        .LeftMargin = Application.InchesToPoints(0.4)
        .RightMargin = Application.InchesToPoints(0.4)
        .BottomMargin = Application.InchesToPoints(1.2)
        .TopMargin = Application.InchesToPoints(1.0)
        .CenterFooter = "&P"
        .RightFooter = ""
        .LeftFooter = Date()
        .CenterHeader = ""
        .RightHeader = Time()
        .LeftHeader = ""
        .PrintTitleRows = "$1:$1"


    End With

Next ws

By setting CenterFooter to "&P", the page numbers will be tracked, adjusted for multi-page worksheets and properly incremented. Also, it’s possible to pass an offset to start page numbering on something other than 1. For example, if page numbers should start with 7 as opposed to 1, set CenterFooter as follows:

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets

    With ws.PageSetup

        .PrintArea = ws.Range("A1").CurrentRegion.Address
        .CenterHorizontally = True
        .CenterVertically = False
        .PrintGridlines = False
        .Orientation = xlPortrait
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = False
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.6)
        .LeftMargin = Application.InchesToPoints(0.4)
        .RightMargin = Application.InchesToPoints(0.4)
        .BottomMargin = Application.InchesToPoints(1.2)
        .TopMargin = Application.InchesToPoints(1.0)
        .CenterFooter = "&P+" & 6
        .RightFooter = ""
        .LeftFooter = Date()
        .CenterHeader = ""
        .RightHeader = Time()
        .LeftHeader = ""
        .PrintTitleRows = "$1:$1"


    End With

Next ws

Note that any of the header/footer locations can contain page number. CenterFooter was arbitrarily chosen for demonstration purposes. Header/footer properties not used in the examples are set to "", but they can contain any desired annotations.

With respect to PrintTitleRows, if a header spans multiple rows, pass the row range. To identify the first 3 rows as title rows, specify PrintTitleRows = "$1:$3".

Including Images in Headers

PageSetup exposes properties facilitating the inclusion of images in a document’s header and/or footer. For the next step in our progression, we’ll demonstrate how to use the LeftHeaderPicture property, but keep in mind RightHeaderPicture, LeftFooterPicture and RightFooterPicture are also available and can be utilized in an identical fashion.

To include this image:

baby_sloth

in the left header of each page in the PDF, add the following to the PageSetup clause:

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets

    With ws.PageSetup

        .PrintArea = ws.Range("A1").CurrentRegion.Address
        .CenterHorizontally = True
        .CenterVertically = False
        .PrintGridlines = False
        .Orientation = xlPortrait
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = False
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.6)
        .LeftMargin = Application.InchesToPoints(0.4)
        .RightMargin = Application.InchesToPoints(0.4)
        .BottomMargin = Application.InchesToPoints(1.2)
        .TopMargin = Application.InchesToPoints(1.0)
        .CenterFooter = "&P+"
        .RightFooter = ""
        .LeftFooter = Date()
        .CenterHeader = ""
        .RightHeader = Time()
        .LeftHeader = "&G"
        .PrintTitleRows = "$1:$1"
        .LeftHeaderPicture.Filename = "C:\Documents\babysloth.png"
        .LeftHeaderPicture.Width = 62
        .LeftHeaderPicture.LockAspectRatio = msoTrue


    End With

Next ws

There are several additional image formatting properties exposed by LeftHeaderPicture, many of which can be found here. In our configuration, we’ve passed the image filepath to LeftHeaderPicture.Filename, specified the output image width to LeftHeaderPicture.Width, and indicated that the aspect ratio should be maintained by setting LeftHeaderPicture.LockAspectRatio to msoTrue. The most important property to set when including images in headers or footers is passing "&G" to the header/footer in question. Notice in our page setup, because the image is in the left header, we set LeftHeader to "&G". This enables the image to show up in the PDF. Without setting LeftHeader to "&G", the image will fail to render.

Here is the entire subprocedure, including both page setup and export components:

'=============================================>
' Export worksheets in current workbook to PDF
'=============================================>
Sub xlsx2pdf()

  Dim ws As Worksheet
  Dim pdfPath As String
  Dim imagePath As String

  ' specify path for generated PDF =>
  pdfPath = "C:\Documents\xlsx2pdf.pdf"

  ' specify path for image to include in header (if applicable) =>
  imagePath = ""

  ' clear existing PageSetup Options =>
  For Each ws In ThisWorkbook.Worksheets
    ws.PageSetup.PrintArea = ""
    ws.PageSetup.LeftHeaderPicture.Filename = ""
  Next ws

  For Each ws In ThisWorkbook.Worksheets

      With ws.PageSetup

          .PrintArea = ws.Range("A1").CurrentRegion.Address
          .CenterHorizontally = True
          .CenterVertically = False
          .PrintGridlines = False
          .Orientation = xlPortrait
          .Zoom = False
          .FitToPagesWide = 1
          .FitToPagesTall = False
          .HeaderMargin = Application.InchesToPoints(0.5)
          .FooterMargin = Application.InchesToPoints(0.6)
          .LeftMargin = Application.InchesToPoints(0.4)
          .RightMargin = Application.InchesToPoints(0.4)
          .BottomMargin = Application.InchesToPoints(1.2)
          .TopMargin = Application.InchesToPoints(1#)
          .CenterFooter = "&P+"
          .RightFooter = ""
          .LeftFooter = Date
          .CenterHeader = ""
          .RightHeader = Time()
          .PrintTitleRows = "$1:$1"

          If Len(imagePath) > 0 Then

              .LeftHeader = "&G"
              .LeftHeaderPicture.Filename = imagePath
              .LeftHeaderPicture.LockAspectRatio = msoTrue
              .LeftHeaderPicture.Width = 62

          End If

      End With

  Next ws


  ' save worksheets as PDF =>
  ThisWorkbook.Worksheets.Select
  ActiveSheet.ExportAsFixedFormat _
                Type:=xlTypePDF, _
                Filename:=pdfPath, _
                Quality:=xlQualityStandard


End Sub

We’ve included logic to clear existing PageSetup rules, and also wrapped the LeftHeaderPicture properties in an If statement in order to bypass the header image insertion logic if no image path is provided.