Export Data As PDF Excel VBA Macro

In this post we see how to export data in Excel as PDF using VBA macros. The data can be a range or table in a worksheet, the whole worksheet, or the entire workbook. The PDF file is saved to the specified drive path with a given file name. We can choose to open the PDF file after the export.

Macro code:

 
  Sub ExportAsPDF()
 
  Dim targetObj As Object, filePDF As String
 
  'CHOOSE SOURCE - workbook, worksheet, or range
  Set targetObj = ActiveWorkbook  'or Workbooks("myworkbook.xlsx")
  Set targetObj = ActiveSheet  'or Sheets("sheetname") or Sheets(x)
  Set targetObj = Selection  'or Sheets(x).Range("A1:D4")
 
  filePDF = "C:\Users\<username>\Documents\data.pdf"
 
  targetObj.ExportAsFixedFormat _
      Type:=xlTypePDF, _
      Filename:=filePDF, _
      Quality:=xlQualityStandard, _
      OpenAfterPublish:=True
 
  End Sub
 

Macro explained:

• First we declare an object variable for the target object to be exported as PDF. That can be a Range object, a Worksheet object, or a Workbook object. The other variable is a string that stores the drive path and name for the exported PDF file.
• Then we define the object variable “targetObj”, which represents the container with data to be exported as PDF. That can be a range or table in the worksheet, the whole worksheet, or the entire workbook. Choose the one you need and comment the other.
• We need to specify a drive path and name for the file with Excel data saved as PDF. The variable “filePDF” includes a directory in the local drive along the name of the PDF file with extension (.pdf).
• Now that we have everything in place, we use the method ExportAsFixedFormat for the chosen object container and specify the different parameters to export the data in Excel as PDF. ExportAsFixedFormat is a method for the Workbook object, the Worksheet object, and the Range object (also the Chart object), so it’s used similarly for any of them. We set the following parameters:
• Type: the type or format of the file, which can be xlTypeXPS or xlTypePDF
• Filename: the drive path and name of the PDF file to be exported
• Quality: the quality of the PDF file, which can be xlQualityStandard or xlQualityMinimum
• OpenAfterPublish: determines whether to open the PDF file after export from Excel

• The method has a few more parameters, but these are not used very often. The outcome is a PDF file with Excel data, either from a range, a worksheet, or the entire workbook.

This is how we export Excel data as PDF using VBA macros.

Other examples:

• How to Send Meeting Invites From Excel with VBA macros
• How to put Range Data Into Array with Excel VBA macros
• How to Add Sheets From Selection with Excel VBA macros