Export Worksheet to PDF
- IMO, using the
GetSaveAsFilename method of the Application object seems more appropriate.
Sub Pdf_Purchase_Order()
' Define constants.
Const SHEET_NAME As String = "Digital PO"
Const FILE_FILTER As String = "PDF Files (*.pdf),*.pdf"
' Reference the workbook.
Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
' Build the destination folder path based on the path of the workbook.
Dim FolderPath As String: FolderPath = wb.Path ' the same as the workbook
If Len(FolderPath) = 0 Then
MsgBox "The workbook """ & wb.Name & """ was never saved!", _
vbExclamation
Exit Sub
End If
'FolderPath = FolderPath & Application.PathSeparator & "MyPDFs"
' Let the user enter (choose) the file name.
' Note that if the file exists, it will be overwritten without confirmation.
Dim FilePath As Variant: FilePath = Application.GetSaveAsFilename( _
InitialFileName:=FolderPath, _
FileFilter:=FILE_FILTER)
If FilePath = False Then
MsgBox "Abandoned exporting PO to PDF!", vbExclamation
Exit Sub
End If
' Ensure saving the workbook before exporting.
'If Not wb.Saved Then wb.Save
' Reference the worksheet (no need to select).
Dim ws As Worksheet:
On Error Resume Next
Set ws = wb.Sheets(SHEET_NAME)
On Error GoTo 0
If ws Is Nothing Then
MsgBox "The sheet """ & SHEET_NAME & """ doesn't exist in workbook """ _
& wb.Name & """!", vbExclamation
Exit Sub
End If
' Export to PDF.
ws.ExportAsFixedFormat _
Type:=xlTypePDF, Filename:=FilePath, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False
' Inform.
MsgBox "PO exported to PDF.", vbInformation
End Sub
