Excel to PDF using Visual Basic for Applications code example

Excel to PDF using Visual Basic for Applications code example

Excel to PDF

ExportAsFixedFormat method allows to export specific Excel range into PDF format. In this article we will export a range into PDF and save it in a temporary location using Print Area.

Syntax

Range.ExportAsFixedFormat(Type, FileName, Quality, IncludeDocProperties, IgnorePrintAreas, From, To, OpenAfterPublish, FixedFormatExtClassPtr, WorkIdentity)

Parameters

  1. Type: Its mandatory parameter which offers XlFixedFormatType enum having following constants
    • xlTypePDF
    • xlTypeXPS
  2. FileName: Its an optional parameter, refers to complete path of the file where file will be saved post export
  3. Quality: Its an optional parameter, offers XlFixedFormatQuality enum which has following constants:
    • xlQualityStandard
    • xlQualityMinimum
  4. IncludeDocProperties: Its an optional parameter, Set to True to indicate that document properties should be included while exporting to PDF.
  5. IgnorePrintAreas: Its an optional parameter, If set to True, ignores any print areas set when publishing while printing to PDF.
  6. From: Its an optional parameter, page number to export from.
  7. To: Its an optional parameter, page number to export upto.
  8. OpenAfterPublish: It is an optional parameter, If set to True, File will be opened post publishing.
  9. FixedFormatExtClassPtr: It is an optional parameter, its memory pointer to FixedFormatExt class

Example

To generate PDF from Print Area we need to select the range and set print area as shown below:

  1. Select the range that you wish to set with print area
  2. Navigate Page Layout tab
  3. Select Print Area dropdown button
  4. Select Set Print Area command

Code example

Const SOURCESHEET As String = "Sheet1"
Public Sub ExportFileAsPDFCodeExample()
On Error GoTo errh
'Declare worksheet
Dim xlSheet As Worksheet
'Bind sheet reference
Set xlSheet = ThisWorkbook.Worksheets(SOURCESHEET)

'preserve print area
Dim rngName As String
rngName = "Print_Area"

'Boolean flag to show pdf post generate
Dim showFile As Boolean
showFile = True

'File path variable
Dim filepath As String

'Get App data folder path
tmpFolderPath = Environ("APPDATA")

'Create unique file name
tmpFileName = "tmp" & Format(Now, "ddmmhhmmss") & ".pdf"

'Generate pdf
xlSheet.Range(rngName).ExportAsFixedFormat Type:=xlTypePDF, Filename:=tmpFolderPath & "\" & tmpFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=showFile

errh:
If Err.Number <> 0 Then
    Exit Sub
End If
End Sub

Output

Please leave your comments or queries under comment section also please do subscribe to out blogs to keep your self upto date.

Leave a Reply

Your email address will not be published.