PDF

Often, the need is to create a reporting or a presentation or something else using Excel tool and the PDF format can be more readable for your user.

The following VBA macro below present how you can export your sheet or workbook as PDF.

Export a sheet as PDF in VBA

In the example below, from the cells object, we will save the document in PDF using the ExportAsFixedFormat VBA method. As an argument, it is imperative to specify xlTypePDF for the type parameter.

Sub SaveSheetPDF()
'Save one sheet as PDF

   ThisWorkbook.Sheets("Weather").Cells.ExportAsFixedFormat _
      Type:=xlTypePDF, FileName:="Desktop", Quality:=xlQualityStandard, _
         IncludeDocProperties:=True, IgnorePrintAreas:=False, _
            OpenAfterPublish:=True

End Sub

Export several sheets as PDF in VBA

To export multiple sheets to PDF, simply create a sheet table and use the ExportAsFixedFormat method as previously.

Sub SaveSeveralSheetsPDF()

    Sheets("Maps").Range("A1:I49").Select 'Select the range
    Sheets("Weather").Activate 'Active the other sheet
    ActiveSheet.Range("A1:I49").Select ' Select the range
    Sheets(Array("Maps", "Weather")).ExportAsFixedFormat _ 
       Type:=xlTypePDF, FileName:="Desktop", _
          Quality:=xlQualityStandard,IncludeDocProperties:=True, IgnorePrintAreas:=False, _
             OpenAfterPublish:=True

End Sub

Export all sheets separately as PDF in VBA

You will find below a procedure with a For Each loop which allows you to export each sheet of your workbook to a PDF.

Sub SaveSheetsPDF()

    Dim Sh As Sheet
    
    For each Sh in ActiveWorbook.Sheets
       Sh.ExportAsFixedFormat Type:=xlTypePDF, _
          FileName:=Sh.Name, Quality:=xlQualityStandard, _
             IncludeDocProperties:=True, IgnorePrintAreas:=False, _
                OpenAfterPublish:=True
    Next Sh

End Sub

Export a workbook as PDF in VBA

You can also use the ExportAsFixedFormat method on a workbook directly.

Sub SaveWbkPDF()

    ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, _
       FileName:="Desktop", Quality:=xlQualityStandard, _
          IncludeDocProperties:=True, IgnorePrintAreas:=False, _
             OpenAfterPublish:=True
End Sub

Export a range as PDF in VBA

In the same way, it is also possible to export to PDF a well-defined cell range.

Sub SaveWbkPDF()

    ActiveSheet.Range("A1:C40").ExportAsFixedFormat Type:=xlTypePDF, _
       FileName:="Desktop", Quality:=xlQualityStandard, _
          IncludeDocProperties:=True, IgnorePrintAreas:=False, _
             OpenAfterPublish:=True
End Sub

Export a chart as PDF in VBA

If you only need the chart, you can also export the chart object.

Sub SaveChartPDF()

    ActiveChart.ExportAsFixedFormat Type:=xlTypePDF, _
       FileName:="Desktop", Quality:=xlQualityStandard, _
          IncludeDocProperties:=True, IgnorePrintAreas:=False, _
              OpenAfterPublish:=True
End Sub

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s